small code to find last column with data

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,584
Office Version
  1. 365
Platform
  1. Windows
The below macro always returns zero instead of the last column number with any data in it.

Code:
Sub Findcolumn()
Dim Lastcolumn As Range, ws As Worksheet
With ThisWorkbook.Worksheets("AB")

Set Lastcolumn = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

End With
End Sub

Edit : Basically I want to Copy last used column of sheet(XYZ) to sheet (ABC)
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The below macro always returns zero instead of the last column number with any data in it.

Code:
Sub Findcolumn()
Dim Lastcolumn As Range, ws As Worksheet
With ThisWorkbook.Worksheets("AB")

Set Lastcolumn = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

End With
End Sub

Edit : Basically I want to Copy last used column of sheet(XYZ) to sheet (ABC)

lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
 
Upvote 0
... Basically I want to Copy last used column of sheet(XYZ) to sheet (ABC)
Try
Code:
ThisWorkbook.Sheets("XYZ").Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).EntireColumn.Copy ThisWorkbook.Sheets("ABC").Range("A1")
Note: ThisWorkbook refers to the workbook that contains the code, not necessarily the active workbook.
 
Upvote 0
Thanks. This looks amazing. Will give this a try tomorrow and update u
 
Upvote 0
Try
Code:
ThisWorkbook.Sheets("XYZ").Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).EntireColumn.Copy ThisWorkbook.Sheets("ABC").Range("A1")
I don't know why but this code give me Runtime error 1004. Please help.
 
Upvote 0
Many things can cause RTE 1004 -- for example, Sheet "ABC" being protected.

Try this line of code on a new workbook with sheets "ABC" and "XYZ".
 
Upvote 0
I tried to break up the code into three separate line to see where exactly the problem is
I noticed that it is giving Runtime error 1004 on the below highlighted line.

Code:
lastcol = Sheets("LE").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).EntireColumn.Copy
Sheets("CD").Cells(4, 5).Select
[COLOR=#ff0000]ActiveCell.PasteSpecial[/COLOR]


Edit : Got to know where the error was. I was trying to paste the whole column in Cell E4 which is impossible.
Tetra thanks for your help. could you please modify the code to copy only row 9 to 50 instead of the whole column ?
 
Last edited:
Upvote 0
Thanks all for your support.
Finally this code works fine. This was impossible for me but without your help.

Code:
Sub UPDATE_LE()
ThisWorkbook.Sheets("LE").Select
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(9, lastcol), Cells(50, lastcol)).Copy
Sheets("CD").Select
Cells(4, 5).PasteSpecial xlPasteValues
End Sub
 
Upvote 0
Or this:
Code:
    Sheets("LE").Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).EntireColumn.Resize(50 - 9 + 1).Offset(9 - 1).Copy
    Sheets("CD").Cells(4, 5).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
 
Last edited:
Upvote 0
Tetra, thanks, That works perfectly.

could you please explain the below line ?

Resize(50 - 9 + 1).Offset(9 - 1)

I have asked you before about resize and offset, but this seems to be different than before.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top