Get next empty Column?

[James]

I want the button to copy from one sheet and each time I'd like it to place
the numbers in the next available column

Do I need to loop and check for emptiness?

Thanks

 

[Norman Jones]

Hi James,

To set a variable Rng to the first empty cell in row 1 on sheet2, try:

Dim Rng As Range
Const rw As Long = 1 '<<====== CHANGE
If IsEmpty(Cells(rw, 1)) Then
Set Rng = Cells(rw, 1)
Else
Set Rng = Cells(rw, Columns.Count). _
End(xlToLeft)(1, 2)
End If
MsgBox Rng.Address


Change the rw value to reflect the row that you use to test for an empty
column.

 

[Norman Jones]

Hi James,

I omitted to qualify the code to refer to Sheet2:

Dim Rng As Range
Const rw As Long = 1 '<<====== CHANGE

With Sheets("Sheet2") '<<====== CHANGE
If IsEmpty(.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count). _
End(xlToLeft)(1, 2)
End If
End With
MsgBox Rng.Address

 

[James]

Thanks ALOT!! That did the trick!!

Hi James,

I omitted to qualify the code to refer to Sheet2:

Dim Rng As Range
Const rw As Long = 1 '<<====== CHANGE

With Sheets("Sheet2") '<<====== CHANGE
If IsEmpty(.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count). _
End(xlToLeft)(1, 2)
End If
End With
MsgBox Rng.Address