This is a discussion on small code to find last column with data within the Excel Questions forums, part of the Question Forums category; The below macro always returns zero instead of the last column number with any data in it. Code: Sub Findcolumn() ...
The below macro always returns zero instead of the last column number with any data in it.
Edit : Basically I want to Copy last used column of sheet(XYZ) to sheet (ABC)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
Last edited by snjpverma; Apr 20th, 2017 at 12:26 PM. Reason: add extra info
for more go to rodericke.com/xlsuper
Thanks. This looks amazing. Will give this a try tomorrow and update u
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".
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 ActiveCell.PasteSpecial
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 by snjpverma; Apr 21st, 2017 at 10:35 AM.
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
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 by Tetra201; Apr 21st, 2017 at 10:57 AM.
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.