0

This line of code works perfectly:

Set Rng = Worksheets("AUX").Range("a1:a14").SpecialCells(xlCellTypeBlanks)

in the sense that effectively the variable Rng is assigned with the proper range. However, if I use the CELLS properties to define the range by using this code line:

Set Rng = Worksheets("AUX").Range(Cells(1, 1), Cells(14, 1)).SpecialCells(xlCellTypeBlanks)

the range is not set (i.e. after code line execution the Rng value is "nothing").

But shouldn't .Range("a1:a14") be equivalent to .Range(Cells(1, 1), Cells(14, 1)) ? It is hours I am trying to find in Internet why the second code line does not set Rng, without success up to now. Can anybody give a tip please?

Guille
  • 326
  • 2
  • 10
  • You should fully qualify all the used ranges. I mean, please try: `Dim wsA As Worksheet` `Set wsA = Worksheets("AUX")` then `Set Rng = wsA.Range(wsA.Cells(1, 1), wsA.Cells(14, 1)).SpecialCells(xlCellTypeBlanks)`. Otherwise, `Cells(1, 1)` refers "A1" **in the active sheet**. If "AUX" sheet is the active one, the range should be set. And, if no any empty cells in the respective range, it will raise an error. That's why, it usually is used between `On Error Resume Next` and `On Error GoTo 0`. Then checking: `If Not Rng is Nothing Then` do something... – FaneDuru Jun 12 '22 at 17:11
  • You can avoid this type of error by using the [Resize](https://learn.microsoft.com/en-us/office/vba/api/excel.range.resize) property, i.e. `Set Rng = Worksheets("AUX").Cells(1, 1).Resize(14, 1).SpecialCells(xlCellTypeBlanks)` – Spectral Instance Jun 12 '22 at 17:35
  • One could say that `.Range("A1:A14")` is equivalent to `.Range(.Cells(1, 1), .Cells(14, 1))` (note the dots in front of both `Cells`) i.e. if you would use a `With` statement you could e.g. do `1: With Worksheets("AUX") 2: Set rng = .Range(.Cells(1, 1), .Cells(14, 1)).SpecialCells(xlCellTypeBlanks) 3: End With`. Also, note that your worksheet is not qualified i.e. if the wrong workbook is active you'll end up with another unexpected behavior. For the workbook containing this code, e.g., you should use `ThisWorkbook.Worksheets(...)`. – VBasic2008 Jun 12 '22 at 21:28
  • @FaneDuru of course Fane you are right, when I call cells prop. the active sheet was the wrong one and that creates the problem. It also works if I activate the right sheet (i.e. "AUX") just before setting Rng. You're really a guru – Guille Jun 13 '22 at 18:22
  • @VBasic2008 of course your alternative is valid and also works fine. Thanks to all you guys! – Guille Jun 13 '22 at 18:22

1 Answers1

0

Thanks to the comments received I found the answer:

Set Rng = Worksheets("AUX").Range(Cells(1, 1), Cells(14, 1)).SpecialCells(xlCellTypeBlanks)

did not work because Cells prop. was referred to the active worksheet which was not, by mistake, the sheet "AUX" to which it should have been referred!

Guille
  • 326
  • 2
  • 10