Range Efficeincy

[Steven]

Hello,

Please can someone show me a peice of code that select the cells from a
column where there is an existing value excluding the column header?

I currently have a workbook where I'm trying to populate a number of
combo boxes using a variable worksheet, this worksheet is regularly
updated by other users but I need my code to select all popluated
cells.

For example I need to call a range of week numbers that reside in
column A, these may change frequently so this range needs to be
flexible enough to pick up all cells.


Thanks in advance.

 

[Norman Jones]

Hi Steven,

Try something like:

'=============>>
Public Sub Tester004()
Dim rng As Range
Dim LRow As Long
Const col As String = "A" '<<==== CHANGE

LRow = Cells(Rows.Count, col).End(xlUp).Row

Set rng = Range("A2:A" & LRow)

End Sub
'<<=============

 

[Steven]

Thanks!!!

 

[Steven]

Is there anyway of not hard coding the column to A?

Currently I have different variables stored from Cols A to G?

Apologies I'm new to this.

 

[Norman Jones]

Hi Steven,

Try:

'=============>>
Public Sub Tester004()
Dim rng As Range
Dim LRow As Long
Const col As String = "A" '<<==== CHANGE

LRow = Cells(Rows.Count, col).End(xlUp).Row

Set rng = Range(col & "2:" & col & LRow)

End Sub
'<<=============