finding the next empty column

[Peter]

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.

 

[Joel]

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Sht2LastRow = _
Sheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Range("A" & Sht2LastRow)
End With
End Sub

 

[FSt1]

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

 

[Joel]

Small change

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Sht2LastCol = _
Sheets("Sheet2").Cells(1,Columns.Count).End(xltoleft).Row
Sh2NewCol = Sht2LastCol + 1
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Cells(1,Sh2NewCol)
End With
End Sub

 

[RyanH]

Peter, I cleaned up the code to make if more readable. This will do what
you are needing.

Option Explicit

Sub CopyRange()

Dim lngLastRow As Long
Dim lngLastColumn As Long

' find last row in Col.A on Sheet1
lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

' find last column in Row 1 on Sheet2
lngLastColumn = Sheets("Sheet2").Cells(1,
Columns.Count).End(xlToLeft).Column

' copy range on Sheet1 to Sheet2
Sheets("Sheet1").Range("A1:A" & lngLastRow).Copy _
Destination:=Sheets("Sheet2").Cells(1, lngLastColumn + 1)

End Sub

I hope this helps! If so, please click "Yes" below.

 

[RyanH]

xlToRight will not work. It will go to the end of the spreadsheet not to the
last column with data in it. In this case xlToLeft is needed.

 

[Peter]

ryan,
many thanks mate!
this works fine. it also can tolerate a blank column when there is a column
to the right of it. ie it skips this col and finds the last column with
somehting in it. this is what i wanted so thankyou for your help.
peter

 

[Peter]

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]

hi joel,
the first version of your suggestion was good but it put the column after
the last full cell in the first col. its not quite what i wanted but its
going to come in handy in the future for these cases.
many many thanks for helping me.

 

[FSt1]

hi
i assumed that you had a solid block of data becasue you said......modify the code a tad...change the destintion......
Sheets("Sheet2").Range("IV1"). _
End(xlToLeft).Offset(0, 1)

that should fix it.
Regards
FSt1