Finding last used Column with data in particular Row – Excel VBA Examples

Description:

There are certain situations where we perform some tasks by finding last used Column with data in a Row. For examples, There may be 100 rows and each row may have the data in certain number of columns which ic not fixed in all the rows. In this situation we loop through the rows and need to find the last used column in each row and do what ever we need to do.

Solution(s):

We can use count the number of columns using in the active sheet from there we can come back in particular row to get the exact column with data. We can use Column property to get last used Column .

• Finding last used Column with data in particular Row – Example
• Finding last used Column with data in particular Row – Case Study
• Download: Finding last used Column with data in particular Row – Example File

Finding last used Column with data in a Row – Example

The following example will show you how to find last column with data in a particular row. In this example we are finding the last used column in Row 1.

Sub sbLastColumnOfARow()
'In this example we are finding the last Columns of Row 1


    Dim lastColumn As Integer
    With ActiveSheet
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox lastColumn
End Sub

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a New Module from Insert Menu
4. Copy the above code and Paste in the code window
5. To check, enter some data in a first row at E1
6. Now Run a Macro, you should see a message box with result 5

Output:

Here is the example data and screen-shot with output.

Finding last used Column with data in a Row – Case Study

The following example will show you real time example and when need find the last column with data in each row.

Requirement:

1. User wants to paste his data for 5 Departments
2. Number of items in each row could be any thing between 1 to 15 (it is not fixed in all the rows)
3. Find maxim sales from 5 departments and Bold the Font

Code:

Sub sbLastColumnInSpecificRow()

Dim lastColumn, lCol As Integer
Dim iCntr, jCntr, iMaxCol As Integer
Dim vMax

For iCntr = 1 To 5
     vMax = 0
     iMaxCol = 2
     
    'Finding last row of current row
    With ActiveSheet
        lastColumn = .Cells(iCntr, .Columns.Count).End(xlToLeft).Column
    End With
    
     lCol = lastColumn
     Range(Cells(iCntr, 2), Cells(iCntr, lCol)).Font.Bold = False
    For jCntr = 2 To lCol
        If vMax < Cells(iCntr, jCntr) Then
            vMax = Cells(iCntr, jCntr)
            iMaxCol = jCntr
        End If
    Next
    
    Cells(iCntr, iMaxCol).Font.Bold = True
Next


End Sub

Instructions:

Download the file below and Click on the FindMax button and Explore the Code.

Output

Here is the scree-shot of the example which is explained above.

Download Example File

Download the example file and Explore it.

ANALYSISTABS -Last Column with Data in a row