range help!

[ummjay]

Hi there,

I have a worksheet, that has data, but want to start the capture on line 9.
I tried to use the below to find the last row for S9, but seems when running this, it only copies/pastes F9. I would like it to continue until the last row of S9:end. Note that S9 starts with values, then has blank values, but further down has more text. Ideally I'd like to stop this at the 1st blank from the top.

Dim lrD&: lrD = ActiveSheet.Cells.Find("*", [S9], _
xlFormulas, SearchDirection:=xlPrevious).Row

   'For understanding LR = Last Row

   'LRD = Cells(Rows.Count, 13).End(xlUp)


Set Siderng = Range("F9:F" & lrD)

when I tried this, it pulled in 300 ish line items, which does not take into account of the blank row stop point.

LRD = Cells(Rows.Count, 13).End(xlUp)

Any ideas?

 

[RobP]

The number 13 in the second piece of code indicates your column number that it will use to search for the last row. So that is Column "M".

Yet, you indicate above you want to look in Column "S", which for me is col num 19.

Does replacing 13 with 19 get you what you need ?

cheers
Rob

 

[NoSparks]

try something along the lines of

lrD = ActiveSheet.Range("S9:S" & Rows.Count).Find("").Row - 1

 

[ummjay]

that worked, thank you!