How to build a range from the last occupied cell in column?

[Rexxx]

What is the VB command to build a range beginning with the last occupied cell in a certain column plus 170 rows?

 

[DaleWatson123321]

Rexxx,

I first submitted the following routine in the Microsoft Office forum - thread68-363648.

This routine will do the job...

It requires that you first assign a range name to the "certain column" you refer to. I've used the name "cert_colm".

Then, when you run the routine, it will:
a) assign the range name "cert_range" to the range you requested, and
b) highlight that range in yellow.

You will probably want to modify these names and color to suit your own needs. They are only intended as examples.

Sub Set_Range()
Application.Goto Reference:="cert_colm"
curcolm = ActiveCell.Column
FirstCell = Cells(65536, curcolm).End(xlUp).Address
LastCell = Cells(65536, curcolm).End(xlUp).Offset(170, 0).Address
Data_Range = FirstCell & ":" & LastCell
Range(Data_Range).Name = "cert_range"
Application.Goto Reference:="cert_range"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

I hope this is what you wanted. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

As always, there is more than one way to skin a cat, though this is only very slightly different from Dale's suggestion

Sub liminal()
Dim iCol As Integer
Dim lRow As Long
iCol = ActiveCell.Column
lRow = Cells(65536, iCol).End(xlUp).Row
Range(Cells(lRow, iCol), Cells(lRow + 170, iCol)).Name = "hullaballoo"
Range("hullaballoo").Interior.Color = vbRed
End Sub

One thing to watch for is that if your last data row is greater than 65366 the routine will lie down and die. DEAD!

And so ends my (shortened)lunch time special for today.

Happy Friday - to you all
(bored? me??)

;-) If a man says something and there are no women there to hear him, is he still wrong?