Macro to delete all rows where column A is actually blank or "", on specified sheets

[jenmwentworth]

I have a workbook that I need to be able to delete and add names to and resort when necessary. The idea is we would delete and add the names on the "Personal Entry" tab only and get the data to flow to the other tabs. The first step I am trying to do is deleting a name on the "Personal Entry", and then running a macro to delete the rows on the other tabs, of the specified sheets, where column A no longer has a name in it.

I was using a delete macro and it works on my "Personal Entry" tab, but my problem is it is all formulas that make the data flow to the other tabs, so it is not identifying the column A as blank in the other sheets, because there is a formula in it. Please how do I fix this?

Sub DeleteBlankRows()

'Delete Blank Rows For Terminated Employees

'Turn off Screen Updating
Application.ScreenUpdating = False

' Defines variable
Dim ws As Worksheet
Dim lastRow As Long 'This is the last row so code knows where to start

' Defines variable nSheets as the sheets you want to ignore
nSheets = Array("Bogey", "Data Imported")

'Defines variable lastRow as last row of Column A
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' For each sheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets

    ' If the sheet name is not in the list nSheets then...
    If Not IsNumeric(Application.Match(ws.Name, nSheets, 0)) Then
    
        'Deletes Rows where Column A is empty
       On Error Resume Next
            With ws.Range("A3:A1" & lastRow)
                .Value = .Value
                .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            End With
    End If

' Check next sheet
Next ws
        
'Turn on Screen Updating
Application.ScreenUpdating = True

End Sub

Commission Worksheet-Kitchen-sort testing recovered 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sales Person Full Name	Commission Rate	Bonus Comm. Rate	Bonus Active Amount
2
3	BH - Bridgette Henry	0.0375	0.05	300000
4	CNR - Crystal Rubis	0.0375	0.05	300000
5	JEG - James Gillhooley	0.0375	0.05	300000
6	SLB - Seana Brown	0.0375	0.05	300000
7	TRACI - Traci Ritchey	0.0375	0.05	300000
8
9
10
Personal Entry

Commission Worksheet-Kitchen-sort testing recovered 3.xlsm
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Salesperson	Total January Net Sales $$ (Reported Sales + Sales Adjustments)	Sales Adjustments	Reported January Sales	Gross Margin %%	Net Margin %%	Commission $$'s (Total Sales * Rate)	Bonus markers (Total Sales Lookup)	Commission Bonus $$ (Total Sales Lookup)		Wk 1		Wk 2		Wk 3		Wk 4		Wk 5		OT ADJ.	Total Monthly Commission $$	Total YTD Sales $$
2											Total	OT	Total	OT	Total	OT	Total	OT	Total	OT
3	BH - Bridgette Henry	$55,583.20		$55,583.20	26.30%		$2,084.37	0	$0.00												$ -	$2,084.37	$55,583.20
4	CNR - Crystal Rubis	$0.00		$0.00	0.00%		$0.00	0	$0.00												$ -	$0.00	$0.00
5	JEG - James Gillhooley	$21,895.32		$21,895.32	30.10%		$821.07	0	$0.00												$ -	$821.07	$21,895.32
6	#REF!	$1,581.49		$1,581.49	31.40%		$0.00	0	$0.00												$ -	$0.00	$1,581.49
7	SLB - Seana Brown	$0.00		$0.00	0.00%		$0.00	0	$0.00												$ -	$0.00	$0.00
8	TRACI - Traci Ritchey	$14,937.57		$14,937.57	26.00%		$560.16	0	$0.00												$ -	$560.16	$14,937.57
9
10
11
12
January

Cell Formulas
Range	Formula
G3:G8	G3	=IFERROR(IF((B3>VLOOKUP(A3,Personal,4,FALSE)),(((B3-VLOOKUP(A3,Personal,4,FALSE))*VLOOKUP(A3,Personal,3,FALSE))+(VLOOKUP(A3,Personal,4,FALSE)*VLOOKUP(A3,Personal,2))),B3*VLOOKUP(A3,Personal,2,FALSE)),0)
H3:H8	H3	=VLOOKUP(B3,Bogey,3)
I3:I8	I3	=+H3*1250
U3:U8	U3	=IFERROR((((J3/K3)/2)*L3)+(((J3/M3)/2)*N3)+(((J3/O3)/2)*P3)+(((J3/Q3)/2)*R3)+(((J3/S3)/2)*T3),0)
V3:V8	V3	=G3+I3+U3
W3:W8	W3	=+B3
B3:B8	B3	=+D3+C3

Named Ranges
Name	Refers To	Cells
Bogey	=Bogey!$B$2:$G$28	H3:H8
January	=OFFSET(January!$A$3,0,0,COUNTA(January!$A:$A)-1,23)	G3

 

[rollis13]

Try instead if rows have in column A error #REF!

.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete