Selecting Group of Rows with spaces in between

[VBAEnjoi]

Hi,

I have a s/sheet which has multiple blocks of Rows.
I used Lastrow to get the last row and the Startrow to get the last block of rows.
However, I am unable to select two other blocks above the last block.
There is a single blank row in between these rows.

Lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
Startrow = .Cells(.Rows.Count, "D").End(xlUp).End(xlUp).Row

 

[Fluff]

Keep adding the .End(xlup) until you get to what you need

 

[VBAEnjoi]

Thanks, I tried the same one but didn't work

New range as Lastrow1 and Startrow1
Lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
to
Lastrow1 = .Cells(.Rows.Count, "D").End(xlUp).End(xlUp).Row

and
Start Row from
Startrow = .Cells(.Rows.Count, "D").End(xlUp).End(xlUp).Row

Startrow1 = .Cells(.Rows.Count, "D").End(xlUp).End(xlUp)..End(xlUp).Row

 

[Fluff]

You have an extra . before the last End(xlup)
What exactly are your trying to do?

 

[VBAEnjoi]

Hi Fluff,

Removed the extra . before the last End(xlup)

Have to select say Row 5 to Row 12 - Then enter a Vlookup formula
Then select say Row 14 to Row 25 enter a VLookup formula
Then select say Row 27 to Row 102 and enter another VLookupformula

The Ranges are dynamic and there is one row space between these ranges

 

[Fluff]

Will it always start at row5?

 

[VBAEnjoi]

Yes, it can start from a fixed row say 5 or 1 or 2.
I meant the Start Row will be same across any sheets.
Only it may not necessarily be 5, it could be a 1 or a 2 etc.

 

[Fluff]

What column needs the formula?

 

[VBAEnjoi]

Column G

 

[Fluff]

Maybe something like

Sub AddFormula()
   Dim Rng As Range
   For Each Rng In Range("D:D").SpecialCells(xlConstants).Areas
      Rng.Offset(, 3).FormulaR1C1 = "=vlookup(rc[-3],c10:c11,2,0)"
   Next Rng
End Sub