VBA: Selecting Last Row only of first range of row data

[jaspalsd]

Hi,

I'm probably being really stupid here but I have two different sets of data on the same worksheet.

I know how to find the last row of data on the entire worksheet even with empty cells.

I know how to find all rows of selected data for the first range, e.g. B2:B10 has data and B15:B20 has data but I'm only selecting the first range using

Range(Range("B2"), Range("B2").End(xlDown)).Select

Question is, how am I able to define the last row of the first range, e.g. B10 and not to select the whole range from B2?

I know the answer is something simple so hopefully I'll figure it out before someone replies

 

[Fluff]

Like

Lastrow1=Range("B2").End(xlDown).row

 

[jaspalsd]

Thanks Fluff,

Maybe I did do it right previously but when I applied the extra logic where I wished to insert r1 rows at the end, the rows got inserted after B2 and not B10 for example

Sub testInsertrows()



Dim r1 As Variant
r1 = WorksheetFunction.CountIf(Worksheets("Currrent AMM Log").Range("U:U"), "Sold")


Dim r2 As Variant
r2 = WorksheetFunction.CountIf(Worksheets("Currrent AMM Log").Range("U:U"), "Extension")


Dim SoldLastRow As Long
    SoldLastRow = Sheets("Sold OPL").Range("B2").End(xlDown).Row
    
  
'this will countif the number of times the criteria is met and apply this to the number of rows to be inserted to the main worksheet
 If r1 > 0 Then
 Range("B2:B" & SoldLastRow).Offset(1, 0).EntireRow.Resize(r1).Insert Shift:=xlDown
  
  
 End If




End Sub

 

[Fluff]

Try

Sheets("Sold OPL").Rows(SoldLastRow).Offset(1).Resize(r1).Insert Shift:=xlDown

 

[jaspalsd]

Thanks!

I swear I did Rows(SoldLastRow) before..... must be one of those days!

Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback