Range Efficeincy
 

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.

Range Efficeincy
 

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
'<<=============


---
Regards,
Norman


"Steven" wrote in message
ups.com...
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.

Range Efficeincy
 

Thanks!!!

Range Efficeincy
 

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.

Range Efficeincy
 

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
'<<=============

---
Regards,
Norman



"Steven" wrote in message
ups.com...
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.