Delete Rows with Conditions

Submitted by Dennis Wallentin.

The following macro deletes a specified number of rows after a certain condition (the cell in Column A is empty) is met:

Sub Delete_rows_with_conditions() Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnArea As Range

Dim lnLastRow As Long, lnMoreRows As Long Dim i As Long, j As Long, k As Long

Set wbBook = ThisWorkbook

Set wsSheet = wbBook.Worksheets("Sheet1")

Application.ScreenUpdating = False

'Here we set the number of rows to be deleted, j = 5

With wsSheet

'Here we identify the number of filled rows in column A, which will 'be compared with the last row of the other columns in the next step.

lnLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'Here we loop through columns B and C, and if one column contains 'more filled cells than column A or the previously checked column, 'the variable lnMoreRows will be updated accordingly. For k = 2 To 3

lnMoreRows = .Cells(Rows.Count, k).End(xlUp).Row + 1 If lnLastRow < lnMoreRows Then lnLastRow = lnMoreRows End If Next k

0 0

Post a comment