How to find lastRow for variable columns?

Hello

I believe that it is possible.But can't figure it out.

I have a userform with 2 commandbuttons "English list" and "Spainish List"
On the worksheet I have
An English List of Hospitals in Column A then in Column B it is in Spainish
An English List of Airlines in Column C then in Column D it is in Spainish
An English List of Banks in Column E then in Column F it is in Spainish

and so on for other lists

I want to click on English List CommandButton and I enter in the add new textbox and if I check the bank then the textbox values should be entered at the last available row of the relevent list in the relevent language,

I know this is a full project but I am not requiring fully prepared application . I just want some help on how can I switch the lists last row.

Please lemme know if you have any questions.

Best Regards
Imran Bhatti

Hi Anand ji

Thanks for reply
Problem is that my column will be a variable
May be A if I click the English List button and check Hospital checkbox
Maybe D column ,if I click spainsh List button and check Airline checkbox

LastRow = Sheets("sheetname").Range("your column" & Rows.Count).End(xlUp).Row

The red one will be a variable depending on the English/spainish commandbuttons and the checkboxes.

I usually use:

Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

to establish the last row in a column (A in this case). But since they are lists with no blanks you could use:

lngRow = Application.WorksheetFunction.CountA(Worksheets(1).Columns(1)

You then just call the routine to reset the last row triggered by pressing the English/Spanish buttons. You also need to call it when the checkboxes change. I would also name the ranges something like "English_Hospital", "Spanish_Hospital" etc and reference them explicitly in the code.

Let me make it little bit simple

Suppose there is an English List for Hospitals and Spanish List of Hospitals
If I click on English List commandbutton and also check the Hospital checkbox then I need to determine the last row of the English List in Column A
But
If I click on Spanish Commandbutton and also check the Hospital button then I need to determine the last row of the Spanish List in B column.
Hope this makes sense.

Thank you Anand ji it worked

Dhanaywaad.(Thanks)

Rather than hard-coding the column letters ... keep the text of the headers consistent and call a function to search row 1, match on the header/s you need, and return the column letter / number.

Tnanks Matrix
Unfortunately I am weak in creating functions and Class moduals.