How do I find the last column with data?

by

Lots of ways to do this. The most reliable is find. 

Dim rLastCell As Range

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

MsgBox ("The last used column is: " & rLastCell.Column)

If you want to find the last column used in a particular row you can use:

Dim lColumn As Long

lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column

Using used range (less reliable):

Dim lColumn As Long

lColumn = ws.UsedRange.Columns.Count

Using used range wont work if you have no data in column A. See here for another issue with used range:

See Here regarding resetting used range.

More Related Contents:

  1. Print function selecting wrong Type of Change option excel vba
  2. Format Cell Contents
  3. What is the difference between .text, .value, and .value2?
  4. Excel VBA – How to add dynamic array formula
  5. VBA check if file exists
  6. How can I concatenate strings in VBA?
  7. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  8. Loading addins when Excel is instantiated programmatically
  9. Trying to use variables as values vba to sql
  10. How to call a macro from a button and pass arguments
  11. Copy Excel range as Picture to Outlook
  12. VBA (Excel) Dictionary on Mac?
  13. Paste Excel range in Outlook
  14. Return RGB values from Range.Interior.Color (or any other Color Property)
  15. Refresh Excel VBA Function Results
  16. excel VBA run macro automatically whenever a cell is changed
  17. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  18. VBa conditional delete loop not working
  19. Translate text using vba
  20. Concatenate column headers if value in rows below is non-blank
  21. Update excel sheet based on outlook mail [closed]
  22. Sending mail using Outlook where the Send method fails
  23. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  24. Using a variable for row in (row,column) for a range in VBA
  25. Unicode string literals in VBA
  26. Error13 in Excel VBA in data file
  27. How to get the path of current worksheet in VBA?
  28. How to open a workbook specifying its path
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment