PDA

View Full Version : Column counting and Loop



Akiba
06-29-2010, 02:52 AM
I am trying to define my range dependant on the number of filled cells in each column below is current code


Private Sub Rangedefiner()
'Redefines dynamic range
Application.DisplayAlerts = False 'Turn off warnings
Range("A2", Range("A65536").End(xlUp)).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
Application.DisplayAlerts = True 'Turn on warnings
End Sub



I need to move along a column at a time recreating my new ranges but I am struggling to find the code to move column by column.

Tried using cells(2,1) but found I was keeping the previous column in my selection so obviously doing it wrong.

Heeelp:bug:

Bob Phillips
06-29-2010, 03:02 AM
Private Sub Rangedefiner()
Dim col As Long
Dim i As Long

Application.DisplayAlerts = False 'Turn off warnings

For i = 1 To 13 'A-M change to suit

'Redefines dynamic range
Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp))CreateNames Top:=True
Next i

Application.DisplayAlerts = True 'Turn on warnings
End Sub

Akiba
06-29-2010, 03:38 AM
Brilliant thanks modified code slightly. just for consistency.

Private Sub Rangedefiner()
Dim col As Long
Dim i As Long

Application.DisplayAlerts = False 'Turn off warnings

For i = 1 To 13 'A-M change to suit

'Redefines dynamic range
Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).CreateNames Top:=True
Next i

Application.DisplayAlerts = True 'Turn on warnings
End Sub