Get the last used of Row

[ccs1981]

hi all,

good days.


how to get the last used of row for the range of data (A:W).
if i had draw border to row 300.


'LastRow = Cells(Rows.Count, 1).End(xlUp).Row (this 1 is only can count one column only)
''LastRow = ActiveSheet.UsedRange.Rows.Count (this 1 get the lastrow = 300 because i draw the border even there is blank)


i want to count the last used of row for the range of data which exclude the border i draw to 300

hope can get help from your all.

thank you~

[Mordred]

Perhaps the following will do?

Please Login or Register  to view this content.

[Mordred]

Sorry, I may have mis-understood this.

i want to count the last used of row for the range of data which exclude the border i draw to 300

So if Row 300 hundred has a border and there's a row 301 without a border, you want the variable lRow to be 301?

[ccs1981]

thank you,

but still cannot get the correct last used row.

[ccs1981]

if i only fill in some of the cell until row 25, then it show the last row as 25.
even i had draw the border to row 300

[Mordred]

The code I provided you will set the range that is used. If you just want the last row number then

Please Login or Register  to view this content.

Did you change the

Please Login or Register  to view this content.

to reference the sheet in your workbook that you want to reference? For instance if you want to reference sheet 1 then

Please Login or Register  to view this content.

The code I gave you was referencing sheet 2.

[ccs1981]

yes, i change to sheet 1..
the code you give me cannot count the correct last used row.

i only want to know the last used row.

[Mordred]

Perhaps you should upload a mock workbook.

the code you give me cannot count the correct last used row.

It can and does but obviously there are issues in your workbook that don't agree with the code.

[ccs1981]

i am using office 2010. it just a normal workbook.
please help~

thank you.

[Mordred]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ccs1981]

Please Login or Register  to view this content.

actually i want the last used row to check some of the required column.

[Mordred]

I don't understand why you are not providing a workbook. I don't particularly like guessing all day when it comes to people's requirements. First you want rows now you want columns. Please, upload a mock workbook with a before and after scenario. If you do not, I will not bother any more.

[ccs1981]

sorry, i typo error.
i want the last used row instead of last used column.

[ccs1981]

i upload here my workbook.


My coding at ThisWorkbook.

[Mordred]

Thank you. Which sheet do you want this to run on?

[ccs1981]

the script is running when i want to close the workbook to ensure some of the column is required key in.
it does not allow blank when try to save and close the workbook.

[Mordred]

So, sheet 1, where there is only 1 row used? LastRow is working perfectly in that scenario.

[ccs1981]

yes, you can simply insert any data into the cell.

[patel45]

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

"great" stuff: LR gets the highest row number by default (starting from row 1 as stated), and your loop is obsolete.

@ccs1981:
if your range starts with row 2 while there is only the heading available you will alwas get a notification. Maybe change your code to read

Please Login or Register  to view this content.

Ciao,
Holger

[ccs1981]

thank you so much.

it solved. Really appreciate it. ^^