Solved

Posted on 2012-08-29

Dear experts,

I have this vba code in excel where i can find the last filled row in a range.

This works perfectly when i start my range at A1 but when i start at A27 it returns the last row in range -26 because it starts counting then. i hope someone can help me.

thanks in advance

Cheers,

Mark

I have this vba code in excel where i can find the last filled row in a range.

This works perfectly when i start my range at A1 but when i start at A27 it returns the last row in range -26 because it starts counting then. i hope someone can help me.

```
Function FindRangeRowColumn(xlRange As Range, BeginLastcell As String) As String
Dim Firstrow As Long, lastRow As Long, firstCol As Long, LastCol As Long, strFirstCell As String, strLastCell As String
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = xlRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(xlRange.Rows.Count, i), Cells(xlRange.Rows.Count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i
lastRow = mRow
LastCol = xlRange.Find(What:="*", After:=xlRange.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
strFirstCell = BeginLastcell
strLastCell = Cells(lastRow, LastCol).Address(False, False)
FindRangeRowColumn = strFirstCell + ":" + strLastCell
lastRow = mRow
end function
```

thanks in advance

Cheers,

Mark

12 Comments

for example i have a range like A:22 to C:27. Then it checks every column for the last filled row. if the last row in A22:A27 = A24, the last row in B22:B27 is B:26, and the last row in C22:C27 is C25 I want to return 26.

What it does now i think is return 4 because:26-22 = 4

thanks for helping:)

```
Option Explicit
Sub sample()
FindRangeRowColumn ("A2:D9")
End Sub
Function FindRangeRowColumn(xlString As String) As String
Dim iRow As Long, i As Long, lCol As Long, max As Long, colTrap As Long
Dim xlRange As Range
Set xlRange = Range(xlString)
lCol = xlRange.Columns.Count
max = 0
For i = 1 To lCol
iRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Offset(0, 0).Row
If iRow >= max Then
max = iRow
colTrap = i
End If
Next i
MsgBox Cells(iRow, colTrap).Address
End Function
```

i get 1 as iRow. what i want is 27.

I know this looks stupid because why would you want to get the last filled row if you only have range for 1 row. but that is only because i use this function several times.

You are saying you will get 1 as iRow means only 1 row is filled.

But you still want the 27 because the range has a 27? (X27)?

My range will sometimes not start at A1 but for example A2:C4

If the last filled is C4 it returns 3 because my range is 3(A2,A3,A4) but i want to return 4

I want to seperate the columns and rows because sometimes C4 is empty while B4 is full Hope you understand me a bit.

Ultimately i want to select the range from the firstcell in my range: in this example A2, to my the last row and column(last row = 4) last column = C

now it returns C3 instead of C4

My original function above returns B7

The reason being that the range is set at A2 (hence the 2nd row) so it is counting the filled rows from 2

If you want it to return B8 (and consider that the range is starting from A2) then the modified function would be

```
Sub sample()
FindRangeRowColumn ("A2:D9")
End Sub
Function FindRangeRowColumn(xlString As String) As String
Dim iRow As Long, i As Long, lCol As Long, max As Long, colTrap As Long
Dim xlRange As Range, lRow As Long
Set xlRange = Range(xlString)
lCol = xlRange.Columns.Count
lRow = xlRange.Cells(1, 1).Row - 1
max = 0
For i = 1 To lCol
iRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Offset(lRow, 0).Row
If iRow >= max Then
max = iRow
colTrap = i
End If
Next i
MsgBox Cells(iRow, colTrap).Address
End Function
```

Temp.PNG
may be

```
Function FindRangeRowColumn(xlRange As Range) As Range
Dim x As String
Dim n As Long
Dim c As Long
Set FindRangeRowColumn = Nothing
With xlRange
x = .Address(external:=1)
n = Evaluate("max(if(len(" & x & "),row(" & x & ")))")
c = Evaluate("max(if(len(" & x & "),column(" & x & ")))")
If n * c Then
Set FindRangeRowColumn = .Cells(1).Resize(n - .Row + 1, c - .Column + 1)
End If
End With
End Function
Sub kTest()
MsgBox FindRangeRowColumn(Range("A1:D9")).Address
End Sub
```

Kris

thanks for your response, i think yours is more flexible but i don't really know what you are doing in your code. I guess this works like i want:). Thanks. Maybe you can explain to me how it really works, i understand just a little;)

@Shanan212, your code also works but is less flexible i guess. Thank you for your time.

i'm going to use kris' solution because i think it's more flexible. Thank you both for your effort and time.

Cheers,

Mark

Title | # Comments | Views | Activity |
---|---|---|---|

In Excel 2010, how do I to use countif for a number of tabs listed on the first sheet of a spreadsheet? | 4 | 28 | |

Sum Per Month | 7 | 23 | |

Excel 2013 Drop down help with data | 3 | 36 | |

Excel - If every cell in row has "Done" then give a 100% | 8 | 23 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!