Deleting rows beyond a certain point

[craigg3]

I have a sheet that will have different amounts of data in it. Data is from column A to K. What I need to do is find where column A has the first blank cell of data, and then delete complete rows from the first blank cell in row A.

Example row A1-500 may have data

row a501 may not have data, so I need to delete complete row from row A501 to A1001

 

[JustynaMK]

Hi Craig,

Please test the following code (Alt+F11 -> VBA -> insert a new module):

Sub DeleteRows()
    Dim lngFirstBlank       As Long
    Dim lngLastRow          As Long
    
    With ActiveSheet
    lngFirstBlank = .Range("A1").End(xlDown).Row + 1
    lngLastRow = .UsedRange.Rows.Count
        If CBool(lngFirstBlank * lngLastRow) Then
            .Range("A" & lngFirstBlank & ":K" & lngLastRow).[COLOR=#ff0000]Select[/COLOR]
        End If
    End With
End Sub

If you're happy with the range that macro selects for you, then change Select to Clear in order to delete the data.

Let me know if that works for you.