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

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 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.

find last column with data in a row excel vba

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.

find last column with data in a row excel vba - case study

Download Example File

Download the example file and Explore it.

mongopono.ru -Last Column with Data in a row

LIMITED TIME OFFER
By |April 4th, 2013|Excel VBA|5 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

5 Comments

  1. stella November 24, 2013 at 3:42 PM - Reply

    HI,

    What’s the meaning of :

    1. lCol
    2.iCntr, jCntr, iMaxCol
    3.vmax

    Thanks

    • PNRao November 24, 2013 at 11:33 PM - Reply

      Hi Stella,

      In this example code:
      iCntr, jCntr, iMaxCol are the integer variables where we can store the numbers temporarily.
      iCntr is used to loop through each row in the data. As per the requirement rows are always fixed in this example (i.e; 5 rows). So the first loop will be
      For iCntr = 1 To 5

      Next iCntr

      jCntr is used to loop through the columns of data which is not fixed in the given requirement, lCol is the variable where we are finding the last column number in each row and storing in lCol. So the second loop will be
      For jCntr = 2 To lCol

      Next jCntr

      iMaxCol is used to store the column number of maximum value.

      vmax is used to store the maximum value for comparing with the next number.

      Here are more explanation on Variables:
      Variables and Data Types in Excel VBA
      and
      Scope of Variables in Excel VBA

      May I recommend you to subscribe to our News Letter to get daily updates and tips on Excel VBA.

      Hope this helps.
      Thanks-PNRao!

  2. Sofia February 21, 2015 at 6:33 AM - Reply

    Thanks in advanced, hope I get help from this site. what I want is to fixed rows for copying in sheet Inv and fixed columns but not rows. Check the code below may be it’ll explain you more.

    Private Sub CommandButton1_Click()Application.ScreenUpdating = FalseDim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets(“Details”)
    Set ws2 = Sheets(“Inv”)
    Set ws3 = Sheets(“Reg”)
    DestRow = ws3.Cells(Rows.Count, “A”).End(xlUp).Row + 1
    ws1.Range(“A4”).copy ———————on next click it should copy A5 then A6
    ws3.Range(“A” & DestRow).PasteSpecial xlPasteValues
    ws1.Range(“B4”).copy ———————on next click it should copy B5 then B6
    ws3.Range(“D” & DestRow).PasteSpecial xlPasteValues
    ws1.Range(“C4”).copy ———————on next click it should copy C5 then C6
    ws3.Range(“G” & DestRow).PasteSpecial xlPasteValues
    ws2.Range(“B13”).copy ———————this should be fixed rows for copying
    ws3.Range(“N” & DestRow).PasteSpecial xlPasteValues
    ws2.Range(“H13”).copy ———————this should be fixed rows for copying
    ws3.Range(“L” & DestRow).PasteSpecial xlPasteValues
    ws2.Range(“I28”).copy ———————this should be fixed rows for copying
    ws3.Range(“J” & DestRow).PasteSpecial xlPasteValues
    ws2.Range(“H15”).copy ———————this should be fixed rows for copying
    ws3.Range(“K” & DestRow).PasteSpecial xlPasteValues
    Application.ScreenUpdating = True
    End Sub

    However it should paste in next available row in Reg sheet

  3. Sofia February 21, 2015 at 6:37 AM - Reply

    Sorry If I’m asking a super easy question but I’m really new to VBA

  4. Rx_ March 22, 2017 at 8:05 PM - Reply

    On office 2016
    Sub sbLastColumnOfARow()
    ‘In this example we are finding the last Columns of Row 1
    Dim lastColumn As Integer
    Range(“a5”).Select
    ‘Rows(“5:5”).Select
    With ActiveSheet
    lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox lastColumn
    End Sub

    Always ends up with 1 as the result regardless of where it starts

Leave A Comment


Related pages


workbooks.close vbaexcel apply chart templateexcel vba if multiple conditionshow to use vlookup functionhow to make pivot tables in excel 2010find duplicates in excel 2010vba find last row with datarun macro without opening workbookaccess vba tutorial pdfmeeting minute template worddata types vbasql ddl dml dcldelete duplicate entries excelexcel dashboardingcolor index excel vbabatch file append to text fileexcel save worksheetcarriage returns in excelload userformms word vba tutorialaccess vba close tablesql statement in vbaopen excel file vbalistbox excelvlookup for dummies 2010excel macro concatenateexcel 2007 timeline templateexcel vba thisworkbookvba msgbox vbcriticalformula to find duplicates in excelexcel offset functionexcel vba lookup value in arraymacros not working in excel 2013vba columnsexcel how to unhide column aexcel dynamic graphwhat is sumif functionexcel formula isna vlookupvisual basic 6.0 interview questions and answers pdfexcel vba programming for dummies pdfmicrosoft excel tables tutorialmacros in sas tutorialvba loopvbscript filecopyvba excel button propertieschecking duplicates in excelexcel merge cells formulaunprotect excel sheet passwordvba excel torrentlast cell in column vbaexcel chart wizard 2010free excel 2013 add insinterview questions on testing for freshers with answersdelete range vbaexcel vba select rowvba copy rangeadvance filter in excelhow to find out duplicates in exceldelete empty rowsreturn vbamerging excel cellslogical function in excelvb to access database connection codematch excel formulamerge excel worksheets without copying and pastingsum range vbaexcel vba worksheetfunctionshortcut key to insert row in excel 2010import worksheet excelhow to learn vlookupunprotect excel sheet with passwordpaste special values macroexcel how to merge columnsexcel vba comboboxunprotect sheet excel 2013vba adodb reference