I’m trying to find an effective way to get the last row/column which have data on it.
I have no problem with VBE, but a big problem with VBA compiler as it refused all my current working functions.
It is really annoying me as this compiler does not support vb constants ie. xlUp, xlLastCell, xlToLeft which are in my current functions.
Any one kind enough to share your working functions to find last row/column with VBA compiler?
We found why the Excel constants were not correctly defined in the compiler and this will be back in the incoming update.
Thanks for that.
When I were trying to get around this issue, I encountered another problem with the compiler. The below code just refused to work. I really don’t know what going wrong here.
Sub MAIN_XP (Param1)
Dim sh as worksheet
Set sh = Application.Worksheets(“Sheet1”)
'LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row ’ this line of code works perfectly in VBA
Dim ct as long
Dim IsLastRow as Boolean
Dim CelValue as variant
Dim LastRow as Long
sh.Cells(1,1).Value = 1234.456789
sh.Cells(2,1).Value = “sample string”
IsLastRow = FALSE
ct = 1
Do Until (IsLastRow = TRUE)
CelValue = sh.Cells(ct,1).Value
If CelValue = “” then IsLastRow = TRUE
ct = ct +1
Loop
LastRow = ct -1
Msgbox (LastRow)
End Sub
Which error do you get?
I got the following error msg when compiling the code.
Unknown identifier or variable is not declared: ‘xlUp’.
Source position: 282,51
xlUp is now defined in the VBA compiler in XLS Padlock 3.
The compiling error for xlUp is gone. But I couldn’t get the function to work yet though there is no compiling error.
What am I missing here?
Below are the full code that I put into the compiler.
Function GetLastRow(ByVal i As Long, ByRef sh As Worksheet) As Long ’ Given Column number
GetLastRow = sh.Cells(sh.Rows.Count, i).End(xlUp).Row
End Function
Sub MyProtectedSub(Param1)
Dim sh as worksheet
Dim LastRow as Long
Dim i as Long
i = 1
Set sh = Application.Worksheets(“Sheet1”)
LastRow = GetLastRow(i, sh)
MsgBox(LastRow)
End Sub
What message do you get for MsgBox?
The macro didn’t make it to the MsgBox. It crashed at the GetLasRow function as shown in the below screenshot
Same problem as another customer. End(xlUp) is not properly recognized by the compiler, it is mismatched with End XXX such as End Sub. We’ll try to fix that in a future release.