View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
FSt1 FSt1 is offline
external usenet poster
 
Posts: 3,942
Default finding the next empty column

hi
i assumed that you had a solid block of data becasue you said......
"..in the colum immediately to the right of the last colum that has data in it".

modify the code a tad...change the destintion......
Sheets("Sheet2").Range("IV1"). _
End(xlToLeft).Offset(0, 1)

that should fix it.
Regards
FSt1

"Peter" wrote:

hey fst1,
thankyou for helping me. this worked very well. it finds the first empty col
as requested. however, in cases where there is an empty col among others
that have data, it wont skip an emplty column and still find the last one to
the righ of it with somehting in it. i actually didnt specify i wanted this.
i will keep this code to cover the cases where i dont want to skip an empty
column.
thankyou again for all your help.
--
Peter


"FSt1" wrote:

hi
try this
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy _
Sheets("Sheet2").Range("A1"). _
End(xlToRight).Offset(0, 1)
End With
End Sub

all that was added was...End(xlToRight).Offset(0,1)
add to the end of the destination.

works in 03

regards
FSt1


"Peter" wrote:

i am new to vba and am keen to learn from others with more experience.

this came from this ms discussion groups. it copies a range in col 1 of
sheet 1 to col 1 of sheet2
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy Sheets("Sheet2").Range("A1")
End With
End Sub

i am happy to use this but i would like it to be a bit more sophisticated in
choosing where in sheet 2 it pastes.

i would like the destination in sheet 2 to be "..in the colum immediately to
the right of the last colum that has data in it".

could someone pls start me off with a bit of code pls.

thanking you in advance.
--
Peter