Identify first row of UsedRange

[PP3321]

Dear Chandoo Community,

Thank you always for your help.
Today I have 1 problem...can somebody help me?

I want to identify the first row of usedRange.
If I run the following code in the attached sample, it returns me 1, not 2.
I remove filling of colors but it still returns me 1...

?Activesheet.usedRange.row
1



What do you think is the reason for this...?

Thank you in advance...

 

[Nebu]

Hi:

The code is giving the right answer, its giving the number of the first row in the used range of the active sheet, the only workaround is to add 1 to the code say

ActiveSheet.UsedRange.Row+1 ,if the first row is left blank.

Thanks

 

[Deepak]

PP3321

It's because you have copied/imported the data in the sheet thus xl recognized the used range as [B1:C3].

Copy the cells [B2:C2] to another sheet & then check.

 

[jindon]

UsedRange refers to the range that you have edited.
Edited means
Cell format, ConditionalFormatting, data entry, Row height, column width etc.
Your sheet has Cell format/Row height in row1, so it returns 1.
Remove the formatting and row height in row1 and save the workbook and close.
If you reopen the workbook, it should give you 2.

 

[PP3321]

@Nebu,@Deepak @jindon
Thank you all for your help.

I wanted to identify the first row of usedRange because
I wanted to delete empty rows and columns.

If it is a problem with formatting then maybe it is better to loop each cell and use IsEmpty Statement...?

Could you please advise how you would approach this problem?

'Define when the data starts

'If StartColumn is more than 1, then loop each column to delete
StartColumn = ActiveSheet.UsedRange.Column
If StartColumn > 1 Then
For c = StartColumn - 1 To 1 Step -1
Cells(1, c).EntireColumn.Delete
Next c
End If

'If StartRow is more than 1, then loop each row to delete
StartRow = ActiveSheet.UsedRange.Row
If StartRow > 1 Then
For r = StartRow - 1 To 1 Step -1
Cells(r, 1).EntireRow.Delete
Next r
End If

 

[jindon]

Use find method.

Sub test()
    With ActiveSheet
        MsgBox "1st row = " & .Cells.Find("*", .Cells(.Rows.Count * .Columns.Count), , , 1, 1).Row
        MsgBox "1st column = " & .Cells.Find("*", .Cells(.Rows.Count * .Columns.Count), , , 2, 1).Column
    End With
End Sub

If you want to delete

Sub test()
    Dim x As Long, y As Long
    With ActiveSheet
        x = .Cells.Find("*", .Cells(.Rows.Count * .Columns.Count), , , 1, 1).Row
        y = .Cells.Find("*", .Cells(.Rows.Count * .Columns.Count), , , 2, 1).Column
        If x > 1 Then Rows(1).Resize(x - 1).Delete
        If y > 1 Then Columns(1).Resize(, y - 1).Delete
    End With
End Sub

 

[PP3321]

@jindon

Thank you so much for your code.
Can you please explain to me why you multiple Rows.Count with Columns.Count?

Cells(Rows.Count * Columns.Count)

 

[jindon]

This should tell you what it is.

    With ActiveSheet
        MsgBox .Cells(.Rows.Count * .Columns.Count).Address
    End With

Same as

    With ActiveSheet
        MsgBox .Cells(.Rows.Count, .Columns.Count).Address
    End With

The very last cell within a worksheet.

 

[PP3321]

@jindon
Wow thank you for explanation.

Sorry to be pain but 1 final question if you do not mind...
what does it mean to assign the very last cell in this parameter...?

*Help says

it is the cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.

 

[PP3321]

@jindon

For example, I thought this would work too...

Sub test()

Debug.Print Cells.Find("*").Row
Debug.Print Cells.Find("*").Column

End Sub

 

[jindon]

OK, just run the code and see the difference

Sub test()
    Cells.Clear
    Range("a1,c2,e4").Value = 1
    Debug.Print "Row", "Column"
    Debug.Print Cells.Find("*").Row, Cells.Find("*").Column
    Debug.Print Cells.Find("*", Cells(Rows.Count, Columns.Count), , , 2, 1).Column, _
    Cells.Find("*", Cells(Rows.Count, Columns.Count), , , 2, 1).Column
End Sub

 

[PP3321]

@jindon

Thank you so much! I tested it and my code did not work!

I think I got it now.

Help says that, if you do not assign this parameter,
the search starts after the cell in the upper-left corner of the range.

It means that search starts from A2, not A1...

Is this correct...?

 

[jindon]

Correct.
So it needs to be from the last cell in order to find A1, if it has data...

 

[PP3321]

@jindon
thank you so much!!! I learned so much from you today...

 

[jindon]

You are welcome and thanks for the feedback.