Find Row Below

[Big_Dawg]

I want to go to the cell below a range of data. My macro keeps returning the last row + next row for the range. I only want the row below the range not to include the last row itself. How can I change the following?:

LastRow = Range("L" & Rows.Count).End(xlUp).Row
vRange = "L" & (LastRow + 1)
Range(vRange).Select
With Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Clear
End With

 

[JackDanIce]

Not sure what you mean, try adjusting this:

Range("L1").End(xlDown).Offset(1,0).Select

Where L1 is the first row your data is in (change to suit)

 

[stnkynts]

I too am unclear as to what you mean. Maybe:

LastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L" & LastRow + 1).EntireRow.ClearContents

 

[Big_Dawg]

For this example, the range is L9 to the last of L20. I want the macro to select L21 which is a blank cell (the cell below the primary range). The primary range will change from time to time.

 

[mirabeau]

Range("L:L")(Rows.Count).End(3)(2).Select

 

[Big_Dawg]

For example, if the range is L6 to L20, I need to go to Cell L21. How do incorporate into the macro?

 

[Big_Dawg]

Thanks Mirabeau,

Exactly what I am looking for.

 

[mirabeau]

Glad to be of help.

Was your opening macro intended to clear all data below the last used row defined from ColumnL? I wasn't sure about that, but if so you might try

With Range("L" & Rows.Count)
   Range(.End(3)(2), .Cells).EntireRow.Delete
End With

It's rarely if ever that one needs to use select anything within a macro in order to proceed.

And, to explain a bit more, End(3) means Xlup. And the (2) means the next cell down from this.

 

[Big_Dawg]

Outstanding Mirabeau,

Thanks for the information. Extremely helpful!!!!