+ Reply to Thread
Results 1 to 6 of 6

How to add lastCol and lastrow to macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    How to add lastCol and lastrow to macro

    I am trying to apply lastrow lastcolumn, but not getting it right.

    I get the error "The extracted range has a missing or illegal field name"

    Thanks
    Sub CopyToOtherSheet1()
    
            Dim lastRow As Long
            Dim lastCol As Long
            Dim colChr As String
    
    
            With Sheets("360-Tableau")
                lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
                lastRow = .Cells(.Rows.Count, lastCol).End(xlUp).Row
            End With
    
    Sheets("360-Tableau").Range(Cells(1, 1), Cells(lastRow, lastCol)).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Sheets("360").Range("A1"), _
    Unique:=False
    
    Application.DisplayAlerts = False
    Sheets("360-Tableau").Delete
    Application.DisplayAlerts = True
    
    End Sub
    Last edited by capson; 08-18-2013 at 11:46 AM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: How to add lastCol and lastrow to macro

    Hi, capson,

    please add code-tags to your procedure as required per Forum Rule #3.

    Untested:
    Sub CopyToOtherSheet1()
    
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colChr As String
    
    
    With Sheets("360-Tableau")
      lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
      lastRow = .Cells(.Rows.Count, lastCol).End(xlUp).Row
      .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Sheets("360").Range("A1"), _
          Unique:=False
    End With
    
    Application.DisplayAlerts = False
    Sheets("360-Tableau").Delete
    Application.DisplayAlerts = True
    
    End Sub
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: How to add lastCol and lastrow to macro

    Sorry about the code tags

    Thanks for the reply HaHoBe, I tried your code I still get the same error

    The code works great for a static range

    Sub FilterCopyToOtherSheet()
    
    Sheets("Comments-Tableau").Range("A1:E100").AdvancedFilter _ 
    Action:=xlFiltercopy, _  
    CopyToRange:=Sheets("Comments").Range("A1"), _ 
    Unique:=False
    
    Application.DisplayAlerts = False 
        Sheets("Comments-Tableau").Delete 
    Application.DisplayAlerts = True 
    
    End Sub

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: How to add lastCol and lastrow to macro

    Hi, capson,

    in the second example the range is set and VBA recognises that it will refer to Sheet Comments-Tableau.

    With the first code you posted the range should refer to Sheet 360-Tableau but if you donīt qualify the cells as part of the range to refer to that sheet as well VBA will assume the cells to be on the active sheeet.

    In the code you work with Row 2 for getting the last Column while you want to copy starting in Row 1 - that might be the reason for the error. Make sure that all columns have a header. Or maybe post a sample file to check.

    Ciao,
    Holger

  5. #5
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: How to add lastCol and lastrow to macro

    I tried

    With Sheets("360-Tableau")
      lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      lastRow = .Cells(.Rows.Count, lastCol).End(xlUp).Row
      .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Sheets("360").Range("A1"), _
          Unique:=False
    End With
    but does not work.

    I can get this to work

    Sub CopyToOtherSheet2()
    
            With Sheets("Comments-Tableau")
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
    
    Sheets("Comments-Tableau").Range("A1:E" & lastRow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets("Comments").Range("A1"), _
    Unique:=False
    
    Application.DisplayAlerts = False
        Sheets("Comments-Tableau").Delete
    Application.DisplayAlerts = True
    
    End Sub
    Clearly not getting the Column part correct

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: How to add lastCol and lastrow to macro

    Hi, capson,

    maybe check the values for both your variables before applying them (I tested the code on a sample workbook, and it works okay with my setup: range starting with A1, no empty columns/rows in data range).

    Maybe try this:

    Sub CopyToOtherSheetAlternative()
    
    With Sheets("360-Tableau")
      .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Sheets("360").Range("A1"), _
          Unique:=False
    End With
    
    Application.DisplayAlerts = False
    Sheets("360-Tableau").Delete
    Application.DisplayAlerts = True
    
    End Sub
    And if it doesnīt work a workbook with a sample of the non working data would be needed.

    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sum Lastrow formula!!
    By Hurricanefly in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-17-2013, 11:35 AM
  2. 'lastrow =' How long does statement 'work' in macro?
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2012, 08:39 AM
  3. Copy From LastRow to LastRow+7
    By dvent in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2010, 06:56 AM
  4. Reference LastCol VB
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2008, 07:24 PM
  5. Autofill to lastrow
    By Pedros in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-11-2007, 12:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1