0

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?

Community
  • 1
  • 1
Manas Saha
  • 1,477
  • 9
  • 29
  • 44
  • See this link.. http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column Instead of `ws.Cells`, you can specify relevant rows as your range. – Siddharth Rout Jan 18 '13 at 10:29

2 Answers2

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
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • @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* :) – bonCodigo Jan 18 '13 at 10:59
  • 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 :) – Siddharth Rout Jan 18 '13 at 11:02
0

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

Jur Pertin
  • 574
  • 4
  • 9