Macro to Delete Blank Rows in Multiple Sheets, Not Whole Workbook

[jenmwentworth]

I have been working on this macro for what seems like forever and I just can't get it to work.



I have a data set and when employees are terminated their names are removed from the spreadsheet and I need any monthly sales data to also be deleted. Employee names all "feed" from a main "Personal" sheet and this is where names are added and removed. When they are removed from the "Personal" tab, there is still a formula referring to the personal tab so there is actually "" data column A of the line.



There are 12 different sheets that I need the macro to run on all at once to delete the "blank" line(s) for any terminated employees. This is what I have so far. All this does is delete the blank line on the "Personal Entry" tab for the code is located. Also, I really don't want it limited to the range A3:A11, i was just testing that to see if it would work. I would like it to be column A, but my header is in both rows 1 & 2.



I am very new to VBA so please help.



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", "Personal Entry")

'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 Range("A3:A11")
.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		K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Salesperson	Total February Net Sales $$ (Reported Sales + Sales Adjustments)	Sales Adjustments	Reported February 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	$0.00					$0.00	0	$ -												$ -	$0.00	$0.00
4	MM - Melanie	$0.00					$0.00	0	$ -												$ -	$0.00	$0.00
5		$0.00					$0.00	0	$ -												$ -	$0.00	#N/A
6	SLB - Seana	$0.00					$0.00	0	$ -												$ -	$0.00	$0.00
7	CNR - Crystal	$0.00					$0.00	0	$ -												$ -	$0.00	$0.00
8	TRACI - Traci	$0.00					$0.00	#N/A	#N/A												$ -	#N/A	#N/A
9		$0.00					$0.00	0	$ -												$ -	$0.00	#N/A
10
11
February

Cell Formulas
Range	Formula
G3:G9	G3	=IFERROR(IF((VLOOKUP(A3,January,2,FALSE)>VLOOKUP(A3,Personal,4,FALSE)),(B3*(VLOOKUP(A3,Personal,3,FALSE))),IF(VLOOKUP(A3,FebYTD,2,FALSE)>VLOOKUP(A3,Personal,4,FALSE),((VLOOKUP(A3,FebYTD,2,FALSE)-VLOOKUP(A3,Personal,4,FALSE))*VLOOKUP(A3,Personal,3,FALSE))+((VLOOKUP(A3,Personal,4,FALSE)-VLOOKUP(A3,January,2,FALSE))*VLOOKUP(A3,Personal,2)),B3*VLOOKUP(A3,Personal,2,FALSE))),0)
H3:H9	H3	=IF(A3="",0,VLOOKUP(VLOOKUP(A3,FebYTD,2,FALSE),Bogey,3)-(VLOOKUP(A3,January,8,FALSE)))
I3:I9	I3	=IF(A3="",0,VLOOKUP(VLOOKUP(A3,FebYTD,2,FALSE),Bogey,6)-VLOOKUP(A3,January,9,FALSE))
U3:U9	U3	=IFERROR((((J3/K3)/2)*L3)+(((J3/M3)/2)*N3)+(((J3/O3)/2)*P3)+(((J3/Q3)/2)*R3)+(((J3/S3)/2)*T3),0)
V3:V9	V3	=G3+I3+U3
W3:W9	W3	=VLOOKUP(A3,FebYTD,2,FALSE)
B3:B9	B3	=+D3+C3

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

 

[rollis13]

Change this:

With Range("A3:A11")

to this:

With ws.Range("A3:A" & lastRow)

 

[jenmwentworth]

Change this:

With Range("A3:A11")

to this:

With ws.Range("A3:A" & lastRow)

Thank You, that will fix my "range" issue, but it still won't execute this on all the sheets I want it to in my workbook.

 

[rollis13]

Here is where your macro already identifies your sheets, all others will be processed (change entries as needed):

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

 

[jenmwentworth]

Here is where your macro already identifies your sheets, all others will be processed (change entries as needed):

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

Thank you so much, it is working now.

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.