Page 1 of 2 12 LastLast
Results 1 to 10 of 15

small code to find last column with data

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() ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default small code to find last column with data

    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 by snjpverma; Apr 20th, 2017 at 12:26 PM. Reason: add extra info

  2. #2
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    1,459

    Default Re: small code to find last column with data

    Quote Originally Posted by snjpverma View Post
    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
    for more go to rodericke.com/xlsuper

  3. #3
    Board Regular Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,448

    Default Re: small code to find last column with data

    Quote Originally Posted by snjpverma View Post
    ... 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.

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    Thanks. This looks amazing. Will give this a try tomorrow and update u

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    Quote Originally Posted by Tetra201 View Post
    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.

  6. #6
    Board Regular Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,448

    Default Re: small code to find last column with data

    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".

  7. #7
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    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.

  8. #8
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    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

  9. #9
    Board Regular Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,448

    Default Re: small code to find last column with data

    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 by Tetra201; Apr 21st, 2017 at 10:57 AM.

  10. #10
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com