Finding the next blank cell

[Risky Dave]

Hi,

Can someone please explain why this throws up an error "Select method of
range class failed" (on the .Select line)

Sheets("ChartsM").Cells(5000, "N").End(xlUp).Select
Set rCurrentCell = ActiveCell

I have repace it with:

Do
Set rCurrentCell = rCurrentCell.Offset(1, 0)
Loop Until rCurrentCell.Value = ""

which works, but I'd like to undersand what I'm doing wrong.

TIA

Dave

 

[joel]

You would get the error if the cell is protected. A set statement
doesn't select the cell so you don't get the error. Besides the example
that works is going to the row after the protected line. I assume you
must have a header row that is protected and you are trying to add a new
row at the end of your data. You will only get the error when you add
the first row of data after the header.

 

[Jacob Skaria]

Hi Dave

If the sheet is not activated and you try to select a cell; it will return
an error. In the second method you are not trying to select and hence it
works.

Either try
Set rcurrentcell = Sheets("ChartsM").Cells(5000, "N").End(xlUp)

OR
Select the sheet and then try selecting the cell using your code.

If this post helps click Yes

 

[Joel]

You would get the error if the cell is protected. A set statement doesn't
select the cell so you don't get the error. Besides the example that works
is going to the row after the protected line. I assume you must have a
header row that is protected and you are trying to add a new row at the end
of your data. You will only get the error when you add the first row of data
after the header.