CODEWITHSUNDEEP
vbaexcel
papi1992
papi1992

Reputation: 168

First used row in my selected column

I want to clear all cells in my worksheet from first used to last used row in my selected column, but first I must know the first used row in my selected column. How can I find the first row in "X" column? (X may = {A, B, C, ...})

Upvotes: 0

Views: 7053

Answers (3)

Developer
Developer

Reputation: 789

See below with no loop.

Sub ClearCells()
    Dim firstRow As Integer
    Dim lastRow As Integer

    firstRow = Cells(1, 1).End(xlDown).Row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range(Cells(firstRow, 1), Cells(lastRow, 1)).ClearContents
End Sub

Upvotes: 0

Developer
Developer

Reputation: 789

Kindly see the code below. It will skip over all the blank rows in column A represented by second argument of Cells as 1. Then assign the row positions for variables firstRow and lastRow. Last clear the values from that range.

Sub ClearCells()
    Dim i As Integer
    Dim firstRow As Integer
    Dim lastRow As Integer

    i = 1
    Do While Cells(i, 1) = ""
        i = i + 1
    Loop
    firstRow = i
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range(Cells(firstRow, 1), Cells(lastRow, 1)).ClearContents
End Sub

Upvotes: 0

Matt Cremeens
Matt Cremeens

Reputation: 5151

Try

firstUsedRow = Worksheets("Sheet1").Cells(1, 1).End(xlDown).Row

Where the second argument in Cells(1, 1) is the column number (e.g., A=1, B=2, etc.). Sheet1 is the worksheet name of the worksheet in question.

It is possible, though unlikely in your case, that the first row is row 1. In this case, the above code will actually select your last row of the used range. As a safety measure for this case, identify the last row and make sure your first row is either 1 or the row generated by the above code. So

finalRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").UsedRange.Rows.Count, 1).End(xlUp).Row
if finalRow = firstUsedRow then
    firstUsedRow = 1
End If

All together

firstUsedRow = Worksheets("Sheet1").Cells(1, 1).End(xlDown).Row
finalRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").UsedRange.Rows.Count, 1).End(xlUp).Row
if finalRow = firstUsedRow then
    firstUsedRow = 1
End If

Upvotes: 5

Related Questions