Code line to delete two table rows below last filled row

[djbroek]

This macro I have doesn't work because it will deletes two rows directly below the row I'm in that have data in them.
This causes problems because those rows below me may already have data in them.

I need to change the .EntireRow.Delete line below to instead delete the next two blank rows that are beneath the last filled row in the worksheet. I'm just getting started in using VBA and have tried multiple different codes but cannot figure it out. Please help!

[SIZE=3][COLOR=#000000][FONT=Calibri]Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]If Target.Count > 1 Then Exit Sub<o:p></o:p>[/FONT][/COLOR][/SIZE]
<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[SIZE=3][COLOR=#000000][FONT=Calibri]Dim RowNum As Long<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    RowNum = Target.Row<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    <o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    If WorksheetFunction.CountA(Range("D" & RowNum & ":S" & RowNum)) > 1 Then<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    Range("D" & RowNum & ":S" & RowNum).ClearContents<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    Range("D" & RowNum).Select<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    MsgBox "Please only make one entry per row"<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]    Range("D" & RowNum + 1, Range("D" & RowNum + 2)).EntireRow.Delete<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]End If<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]End If<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/COLOR][/SIZE]

 

[hippiehacker]

check if the selected lines are the lines you want to delete

Sub djbroek()
lastrow = Range("A" & Rows.Count).End(xlUp).Row 'lastrow with data
Rows(lastrow + 1 & ":" & lastrow + 2).Select
End Sub

 

[Macropod]

I need to change the .EntireRow.Delete line below to instead delete the next two blank rows that are beneath the last filled row in the worksheet.

This doesn't make much sense. Any rows below the last 'filled' row would already be empty, so what is the point of deleting them?

 

[hippiehacker]

i thought the same but if you use clearcontents anywhere cell formats are still active maybe he wants to delete those??

 

[Macropod]

True, but the same considerations might also apply to rows below those two as well.

 

[djbroek]

I replaced the

Range("D" & RowNum + 1, Range("D" & RowNum + 2)).EntireRow.Delete

with

lastrow = Range("A" & Rows.Count).End(xlUp).Row 'lastrow with data
Rows(lastrow + 1 & ":" & lastrow + 2).Select

and either it prevented another row from being added after the last filled row or it did delete one row below the last filled row. But for some reason it did not delete the second row.

The reason I need two rows taken out after that last filled row after the MsgBox "Please only make one entry per row" event is activated is because I have a double-click macro which adds another row to the table after the last filled row each time a user fills a row. When the MsgBox "Please only make one entry per row" event is activated, it means that the user had tried to double-click another entry on the same row. So then, the row gets cleared and then the user still has to double-click on the row again to enter the desired selection (which then causes another row to be added after the last filled row).

I hope the above paragraph makes some sense. I just wanted to have the same number of blank rows below the last filled row to maintain a desired cosmetic look to the worksheet.

 

[djbroek]

I have several colored rows after the last filled rows which are there to keep from having the glaring white blank rows below the filled rows. I have sensitive eyes and need to keep the white areas out of the computer screen.

 

[Macropod]

I have several colored rows after the last filled rows which are there to keep from having the glaring white blank rows below the filled rows. I have sensitive eyes and need to keep the white areas out of the computer screen.

In that case, might I suggest, you're going about the the entirely wrong way. Whilst shading the worksheet might help your eyes, it also affects affects the printout - as well as requiring extra work to maintain. A better approach would be to use the Windows Control Panel to change the screen brightness and/or the Window colour. These changes will affect all programs - and even the viewing colours of many internet sites.

 

[Macropod]

Cross-posted at: Delete two rows after last filled table row - Tech Support Forum
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters

 

[djbroek]

Sorry about the post on another forum. That won't ever happen again.
I still wish to have an answer to this code problem. The information from this worksheet is linked to another worksheet where data will be printed.
I'm not sure why it would cause problems the users in my group all use the same type of computers and network.