Find last column with data and add data after that column

Question

I have some data in my worksheet and what I want to achieve is that I want to add data after 1 column from the last column with data. I have tried the Range().End property but I think I am not able to implement it correctly.

colnum = Sheet3.Range("A:Z").End(xlToLeft).Column + 2

The above code gives me the column number as '1' and plus 2 means it gives column number '3' which is 'C'. Data in my worksheet is something like this:

I want a dynamic search and my code should find column 'E' as the last column with data and start adding data from column 'F'. Any suggestions are appreciated.

Ron Rosenfeld · Answer 1 · 2020-02-19T01:32:26.173

This function returns an array where the first element is the last row number, and the second element the last column number.

Function LastRowCol(Worksht) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

With WS
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = LastRow
L(1) = LastCol
LastRowCol = L
End Function

Sub change_code_name()
'Requires Trust Access to VBA Object Model

  Dim wbk As Object, sheet As Object
ActiveWorkbook.VBProject.name = "VBAProject"
Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
  wbk.name = "wbk_code_name"
Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
  sheet.name = "sheet_code_name"
End Sub

In the case of your code line, assuming your worksheet CodeName is Sheet3, it would be something like:

colnum = LastRowCol(Sheet3)(1) + 1

and would apply to the active workbook.

See also Error in finding last used cell in Excel with VBA for an extensive discussion of the various methods of finding the last row/column and the pitfalls of each.

Edit:

Examining your screenshot, it seems that you might want to exclude Row 1 from the testing. If that is the case, then try this slightly modified code:

Option Explicit
Function LastRowCol(Worksht, Optional excludeRows As Long = 0) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long
    Dim searchRng As Range

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

If excludeRows > 0 Then
    With WS
        Set searchRng = Range(.Cells(excludeRows + 1, 1), .Cells(.Rows.Count, .Columns.Count))
    End With
Else
    Set searchRng = WS.Cells
End If

With searchRng
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = excludeRows + 1
L(1) = LastCol
LastRowCol = L
End Function

and, in your macro:

colnum = LastRowCol(Sheet3,1)(1) + 1

Find last column with data and add data after that column

1 Answers1