Delete Entire row if A is blank starting at row 3 not working

[zone709]

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub QuickCull2Test() 'Delete if C is blank but starting atrow 3'
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Intersect(Columns("A"),ActiveSheet.UsedRange).Offset(3).SpecialCells(xlBlanks).EntireRow.Delete     <-- breaks
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Breaks not sure why the cell seems blank to me

 

[cerfani]

if the intersect function returns nothing then it would error

 

[zone709]

So how would I get this to work then remove the

Intersect​

? This is a new sheet I'm working on maybe something changed I just couldn't figure out why its not working. Or do I need to use an updated code for it to work? thanks

 

[Rick Rothstein]

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub QuickCull2Test() 'Delete if C is blank but starting atrow 3'
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Intersect(Columns("A"),ActiveSheet.UsedRange).Offset(3).SpecialCells(xlBlanks).EntireRow.Delete     <-- breaks
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Breaks not sure why the cell seems blank to me

I am not sure why that is happening for you (unless all the cells in Column A are blank in which case you could not offset them), but you do not to intersect Column A with the UsedRange as the SpecialCells function automatically restricts itself to the UsedRange. Given that, this code line should work for you...

Range("A3:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow.Delete

 

[zone709]

no some cells are blank some are not. The only thing I am trying to do is if any cells in column A are empty or blank then delete entire row. I changed the code and added what you posted and here are the results as I think the top rows moved. If the case on changing the range to A100:A then ill do that ? These are the results as you can see rows are still there even that some in A are blank.

Excel 2016 (Windows) 32 bit

	A​
1​	payroll_number​
2​	don't put values in this column
3​	02182018​
4​	02182018​
5​	02182018​
6​	02182018​
7​	02182018​
8​	02182018​
9​	02182018​
10​	02182018​
11​	02182018​
12​	02182018​
13​	02182018​
14​
15​	02182018​
16​	02182018​
17​
18​
19​	02182018​
20​
21​	02182018​
22​
23​
24​
25​
26​
27​	02182018​

Sheet: Sheets1

 

[zone709]

I think I know why. Cause I changed some columns to Text to columns delimted because my formulas wouldn't work. This should do the trick



Columns("A").TextToColumns , xlDelimited, , , False, False,False, False, False