VBA code to find max of last used column index within the selected rows

Question

I have a worksheet where there are many rows. I need to get the max column index of the last non empty column within a specified selected rows range.

for example, the sheet contains 200 rows, each row contains 26 columns, but currently only rows 20 to 30 are selected. I need to find the maximum last used column index within these rows.

Please note that there can be empty cells in between non-empty cells in a row. I need to consider the last non empty cell only.

How can I achieve this?

See this link.. stackoverflow.com/questions/11883256/… Instead of ws.Cells, you can specify relevant rows as your range. — Siddharth Rout
– Siddharth Rout, Commented Jan 18, 2013 at 10:29

bonCodigo · Accepted Answer · 2013-01-18 10:52:46Z

0

Well you can try this: Assume you want to get the last column within the range "A2:X50" in Sheet1

Dim LastColumn as Long    
lastCol = Sheets(1).Range("A2:X50").Columns.Count

edited Jan 18, 2013 at 10:52

answered Jan 18, 2013 at 10:33

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bonCodigo Over a year ago

@SiddharthRout brain freeze :$ I still don't get it.. All I know this gives columns between the range specified A2:C10 is 3 then B2:C10 is 2.. I saw your link, I thought of giving the same, but I guess I was mislead here max column index of the last non empty column within a specified selected rows range :)

Siddharth Rout Over a year ago

The OP already knows there are 26 columns :) What he wants is to find the last column in the rows 20-30 which has data. Note it is not necessary that Col X in your example may have data :)

Jur Pertin · Accepted Answer · 2014-10-13 07:24:44Z

Try this, lngMaxColumIndex is the last column index and lngMaxRowIndex is the last row index

Sub pFindLastCells()

Dim rngRange                As Range
Dim wksWorksheet            As Worksheet
Dim lngMaxColumIndex        As Long
Dim lngMaxRowIndex          As Long

'Set 'Sheet1' worksheet
Set wksWorksheet = Worksheets("Sheet1")

With wksWorksheet
    'Check for last non-empty cell
    Set rngRange = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious)
End With

If Not rngRange Is Nothing Then
    'if found then assign last non-empty cell row and colum index to the variable
    lngMaxColumIndex = rngRange.Column
    lngMaxRowIndex = rngRange.Row
Else
    'if not found gives message
    MsgBox "No Data in " & wksWorksheet.Name, vbCritical + vbInformation, "Error"
End If

Set rngRange = Nothing

End Sub

Collectives™ on Stack Overflow

VBA code to find max of last used column index within the selected rows

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related