Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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






  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Go To the next empty cell in Column A DaddyRay Excel Worksheet Functions 4 April 4th 23 11:27 AM
Looping until empty column theguz Excel Discussion (Misc queries) 1 August 4th 05 07:04 PM
Find the first empty column Simon Excel Programming 3 September 6th 04 12:31 PM
Next Empty Column in Row scain2004[_20_] Excel Programming 1 June 16th 04 07:20 AM
Finding last non-empty column in row... Mika[_3_] Excel Programming 2 November 11th 03 08:50 PM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"