Get the last used of Row

Perhaps the following will do?

Sub FindCompleteRange()

    Dim ws2 As Worksheet
    Dim uRng As Range
    Dim lRow As Long, lCol As Long
    
    Set ws2 = Worksheets("Sheet2")
    lRow = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
    lCol = ws2.Cells(lRow, ws2.Columns.Count).End(xlToLeft).Column
    Set uRng = ws2.Range(ws2.Cells(1, 1), ws2.Cells(lRow, lCol))
    Debug.Print uRng.Address
    
End Sub

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?

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

lRow = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

Did you change the

Set ws2 = Worksheets("Sheet2")

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

Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet1")

The code I gave you was referencing sheet 2.

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.

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

thank you.

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)



 
    Dim ws2 As Worksheet
    Dim LastRow As Long

    
    Set ws2 = Worksheets("Sheet1")
    LastRow = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
  

If WorksheetFunction.CountBlank(Range("A2:B" & LastRow)) >= 1 Or WorksheetFunction.CountBlank(Range("D2:D" & LastRow)) >= 1 Or WorksheetFunction.CountBlank(Range("F2:F" & LastRow)) >= 1 Then
        MsgBox "Save disabled because at least one required field is left blank in BOARDTYPE, BOARD, CHASSIS, PIC, STATUS"
    Cancel = True
End If



End Sub

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

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.

i upload here my workbook.


My coding at ThisWorkbook.

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

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

Sub LastRowInRange()
LR = ActiveSheet.UsedRange.Rows.Count
Set rng = Range("A1:W" & LR)
Lastcol = rng.Columns.Count
LastRow = 1
For c = 1 To Lastcol
  If Cells(Rows.Count, c).End(xlUp).Row > LastRow Then LastRow = Cells(Rows.Count, c).End(xlUp).Row
Next
MsgBox LastRow

End Sub

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

If WorksheetFunction.CountBlank(Range("A1:B" & LastRow)) >= 1 Or WorksheetFunction.CountBlank(Range("D1:D" & LastRow)) >= 1 Or WorksheetFunction.CountBlank(Range("F1:F" & LastRow)) >= 1 Then
        MsgBox "Save disabled because at least one required field is left blank in BOARDTYPE, BOARD, CHASSIS, PIC, STATUS"
    Cancel = True
End If

Ciao,
Holger