Copy last column with data on specified row to the next blank column

by

To get the exact column in a worksheet, use this code.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastCol As Long

    Set ws = Sheets("Sheet1")

    '~~> This check is required else .FIND will give you error on an empty sheet
    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastCol = 1
    Else
        LastCol = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    End If

    Debug.Print LastCol
End Sub

EDIT: This is courtesy @brettdj. You can also use the range object to find the last column

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastCol As Long
    Dim rng As Range

    Set ws = Sheets("Sheet1")

    Set rng = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)

    If rng Is Nothing Then
        LastCol = 1
    Else
        LastCol = rng.Column
    End If

    Debug.Print LastCol
End Sub

To get the last column of a particular row, say row 1 use this

    Debug.Print ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Where ws is your relevant worksheet.

Similarly for Row see this.

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Excel VBA deleting rows in a for loop misses rows
  4. What is the lifetime of a global variable in excel vba?
  5. Download attachment from Outlook and Open in Excel
  6. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  7. Embed picture in outlook mail body excel vba
  8. How to check for empty array in vba macro [duplicate]
  9. Use autofilter on more than 2 criteria
  10. What is the function of FormulaR1C1?
  11. Compiler Error: User-defined types not defined
  12. Open a workbook using FileDialog and manipulate it in Excel VBA
  13. Vba Excel do vlookup from a closed file
  14. excel hyperlink to nothing
  15. How to add headers to a multicolumn listbox in an Excel userform using VBA
  16. How to send files via HTTP_POST with Excel using VBA?
  17. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  18. Vba macro to copy row from table if value in table meets condition
  19. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  20. Set a cell value from a function
  21. How to determine if a date falls on the weekend?
  22. What are the differences between using the New keyword and calling CreateObject in Excel VBA?
  23. Excel VBA wildcard search
  24. VBA Check if file (from website) exists
  25. Remove double quotation from write statement in vba
  26. Meaning of .Cells(.Rows.Count,”A”).End(xlUp).row
  27. Missing VBA compiler message for wrong method name
  28. How do I loop an excel 2010 table by using his name & column reference?
  29. Is there a way to make UDF that gives a description like the native Excel functions have when user clicks the Fx button?
  30. How to improve the speed of VBA macro code?

Leave a Comment