Last not empty cell (column) in the given row; Excel VBA

by

I have expanded on my comment above to provide solutions that

  • do not use Select
  • cater for the last cell in row 1 being used
  • cater for the entire row being empty
  • cater for the entire row being full

The Find method in the second code is a far more direct method of establishing the first non-blank cell

This line Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious)
says, start in cell A1 of Sheet “DTCa” then look backwards (ie from the last cell in row 1) in row1 by column looking for anything (the *). This method either find the last non blank or returns Nothing , ie an empty row

using xltoLeft with specific checks

Sub Method1()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("DTCs")
If ws.Cells(1, Columns.Count) = vbNullString Then
    Set rng1 = ws.Cells(1, Columns.Count).End(xlToLeft)
    If rng1.Column <> 1 Then
        'return last used cell
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
    If ws.[a1] = vbNullString Then
            MsgBox ws.Name & " row1 is completely empty", vbCritical
        Else
            'true last used cell is A1
            MsgBox "rng1 contains " & rng1.Address(0, 0)
        End If
    End If
Else
    'last cell is non-blank
    MsgBox ws.Cells(1, Columns.Count) & " contains a value", vbCritical
End If
End Sub

recommended

Sub Method2()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("DTCs")
    Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
        MsgBox ws.Name & " row1 is completely empty", vbCritical
    End If
End Sub

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. So, I have 6 “master” files to then divide into 40 separate files
  5. HTML Text with tags to formatted text in an Excel cell
  6. Split comma separated entries to new rows [closed]
  7. Get list of Excel files in a folder using VBA [duplicate]
  8. Select first empty cell in column F starting from row 1. (without using offset )
  9. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  10. Excel VBA: AutoFill Multiple Cells with Formulas
  11. VBA.Find and adding a value
  12. excel vba- extract text between 2 characters
  13. Round function in Excel, worksheet function vs VBA
  14. How to continue the code on the next line in VBA
  15. How to create a separate CSV file from VBA?
  16. Excel VBA Userform – Execute Sub when something changes
  17. Count the number of rows in another sheet [duplicate]
  18. Getting Excel to refresh data on sheet from within VBA
  19. VBA Password Input with Cancel Function
  20. Excel Select vs Activate
  21. Fast compare method of 2 columns [closed]
  22. Finding first blank row, then writing to it
  23. How does Excel’s worksheet password protection work
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment