gingerbreadgrl
New Member
- Joined
- Aug 19, 2019
- Messages
- 48
Hi,
I know there are a few threads on this but I haven't found one that works. I have a column, column A, that contains headings in the rows. Column B will contain the appropriate data that corresponds to the heading. However, any data that does not apply will be blank in Column B. I would like to declare the sheet, as there are multiple in the workbook, and delete any rows, starting with row 1, where the cell in column B is blank. The sheet name is Record 1. Here is a sample of the data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Child 1[/TD]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Child 2[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Child 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In this example, I would like row 3 to delete because the cell in column B is blank. I have tried using the following code, but neither option worked. Note, this code is placed within a larger macro so there is no end sub.
In this code, the author recommended that certain applications be disabled. No error is produced, but the rows do not delete.
Here is the second code I tried, this will select the first cell in column B that contains a value, but no rows are deleted. Note, the author chose to start the search at row b1000, which works for me.
Any troubleshooting on either piece of code would be much appreciated!!
Best,
Gingerbreadgrl
I know there are a few threads on this but I haven't found one that works. I have a column, column A, that contains headings in the rows. Column B will contain the appropriate data that corresponds to the heading. However, any data that does not apply will be blank in Column B. I would like to declare the sheet, as there are multiple in the workbook, and delete any rows, starting with row 1, where the cell in column B is blank. The sheet name is Record 1. Here is a sample of the data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Child 1[/TD]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Child 2[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Child 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In this example, I would like row 3 to delete because the cell in column B is blank. I have tried using the following code, but neither option worked. Note, this code is placed within a larger macro so there is no end sub.
In this code, the author recommended that certain applications be disabled. No error is produced, but the rows do not delete.
Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim deleteRow As Long
Dim ws As Worksheet
Set ws = Sheets("Record 1")
For deleteRow = ws.Range("B" & Rows.count).End(xlUp).Row To 1 Step -1
If ws.Range("B" & deleteRow).Value = "" Then
Rows(deleteRow).EntireRow.Delete
End If
Next deleteRow[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Here is the second code I tried, this will select the first cell in column B that contains a value, but no rows are deleted. Note, the author chose to start the search at row b1000, which works for me.
Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim xlastrow As Integer
Dim xrow As Integer
xrow = 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Sheets("Record 1").Range("b1000").End(xlUp).Select
xlastrow = ActiveCell.Row[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Do Until xrow = xlastrow[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
If Sheets("Record 1").Cells(xrow, 1).Value = "" Then
Sheets("Record 1").Cells(xrow, 1).Select
Selection.EntireRow.Delete[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]xrow = xrow - 1
xlastrow = xlastrow - 1
End If
xrow = xrow + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Loop[/FONT]
<strike></strike>
[/FONT]
<strike></strike>
Any troubleshooting on either piece of code would be much appreciated!!
Best,
Gingerbreadgrl