Get next empty Column?
 

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

Get next empty Column?
 

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.


---
Regards,
Norman



"James" wrote in message
...
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

Get next empty Column?
 

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


---
Regards,
Norman



"Norman Jones" wrote in message
...
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.


---
Regards,
Norman



"James" wrote in message
...
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

Get next empty Column?
 

Thanks ALOT!! That did the trick!!


"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Norman Jones" wrote in message
...
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.


---
Regards,
Norman



"James" wrote in message
...
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