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

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
72
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

Code:
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 :)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Like
Code:
Lastrow1=Range("B2").End(xlDown).row
 
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0
Try
Code:
Sheets("Sold OPL").Rows(SoldLastRow).Offset(1).Resize(r1).Insert Shift:=xlDown
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top