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

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,911
Code:
[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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
if the intersect function returns nothing then it would error
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,911
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,328
Office Version
  1. 2010
Platform
  1. Windows
Code:
[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...
Code:
Range("A3:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow.Delete
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,911
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

Well-known Member
Joined
Mar 1, 2016
Messages
1,911
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,310
Messages
5,527,939
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top