Excel VBA - Find last used cell of a variable column number?

[Alan_P]

Hi All,

Just wondering if anyone knows if there is a better way of finding the last used row when the column is variable?

So normally I would just use the below code to find the last row;

Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp).Row

But in the code I've done I am finding the column via it's number (x) rather than letter and struggled to fit that into the above piece of code...

I've managed to get around this by finding the address and then using Mid to extract the column letter, but I can't help but think there must be a better way of doing this?

For i = 2 To 6
    RecipeNum = Sheets("Meals").Cells(3, i).Value
    
    For x = 1 To LastRecipe
               
        If RecipeNum = Sheets("Recipes").Cells(3, x).Value Then
        
            ColAddress = Sheets("Recipes").Cells(3, x).Address
            ColLetter = Mid(ColAddress, 2, 1)
        
            LastRecipeIngredient = Sheets("Recipes").Range(ColLetter & Rows.Count).End(xlUp).Row
                       
        End If
    Next
Next

Any suggestions would be appreciated,
Cheers,
Alan.

 

[Andrew Poulsom]

Use Cells instead of Range, like you already have done:

LastRecipeIngredient = Sheets("Recipes").Cells(Rows.Count, x).End(xlUp).Row

 

[Alan_P]

Thanks Andrew!! So simple yet my brain just would not come to that conclusion!!