Deleting Blank Rows in Excel

Microsoft Excel 2003 (full product)
September 20, 2010 at 16:53:29
Specs: Windows XP

I am having some trouble with excel and would really appreciate any help.

I have a master list of attributes in excel. Some have a modbus address listed with them and some dont. Each time we need to add an additional site at our company, we type in the new addresses for each attribute in the list. Not all sites have addresses for all attributes. I'd like to be able to make a new list where the attributes without an address do not populate in the new list.

So basically, if I had a list of 20 attributes, but the site in question had only 10 addresses, i'd like to make a new list of just those 10 attributes and their respective addresses.

I have tried several pieces of coding using VBA and macros but to no avail. Anyone have any ideas on this?

Thank you in advance

See More: Deleting Blank Rows in Excel

Report •

September 20, 2010 at 21:38:36
Do us a favor.

Make believe that you can't see your spreadsheet and that you don't have any idea how it is laid out or how data gets entered into it.

Now go back and read your post and see if you could answer your question.

Personally, I don't see enough details that help us understand what you have or what you are trying to do.

Your subject line says "deleting blank rows" yet you talk about "attributes without an address".

You say you have tried some code, but you don't supply any examples or tell us why they don't work.

Perhaps if you gave us some examples of your input data and the desired output we could offer some assistance.

Report •

September 21, 2010 at 00:27:22
oops....apparently that preview didn't work out so well for me.....

You are right. I should've done a better job describing my problem.

Here is an example of what I am trying to accomplish:

Attribute Name Address
ACLineKW 40001
Avg Voltage 40003
Avg Amps
ExhBackPressure 40007
CoolantTemp 40008
OilPressure 40010
Opened 40001:13
Closed 40001:14
Synched 40001:16

Attribute Name Address
ACLineKW 40001
Avg Voltage 40003
ExhBackPressure 40007
CoolantTemp 40008
OilPressure 40010
Opened 40001:13
Closed 40001:14
Synched 40001:16

The first list would be the master list. The master list would be generic. After entering the job-specific addresses into the master, a new secondary list would be generated, only including those attributes where an address was listed on the master list (like the second list shown). I need to be able to delete entire rows in excel which include a blank cell. The secondary list would be sheet2 in the same excel workbook as the master list.

As far as VBA code I tried (xlcelltypeblanks) in combination with (select). But when using an IF statement to populate the address on my secondary list, the IF 'false' argument doesn't leave the address column 'blank.' I also tried using a nested IF statement but it was inadequate to delete the rows without addresses.
Forgive me for the poor clarity of my original post. I hope this description is better.

Thank you for your help!

Report •

September 21, 2010 at 07:22:46
Here's a couple of suggestions, one that's kind of mundane and one that's kind of fun:

Mundane Method:

You didn't provide the IF statement you are using or the code that you tried, so I'll just toss this out:

This syntax will return a empty cell that VBA can recognize:

=IF(Sheet1!B1="", "", Sheet1!B1)

Then, to use VBA to delete a Row where Column B is empty, you loop through the range in reverse order, checking for "". You must do the deletion in reverse order or the counter may skip rows as it increments.

e.g. If B3 and B4 are both empty, "foward looking" code will Delete row 3, moving Row 4 up 1 row. Then when the counter increments to 4, it will really be looking at whatever used to be in B5, skipping what used to be in B4.

Sub DelBlanks()
 For rwNum = 15 To 2 Step -1
  If Cells(rwNum, 2) = "" Then Cells(rwNum, 2).EntireRow.Delete
End Sub

Fun Method:

Use an Advanced Filter to filter out the Blank Rows on Sheet1.

Start with this layout in Sheet 1:

	A	B
1	Attribute         Address
2		          <>
4	Attribute         Address
5	ACLineKW            40001
6	ACLineKWH	
7	Avg                 40003
8	Avg that	
9	PF	
10	ExhBackPressure     40007
11	CoolantTemp         40008
12	EngineDP	
13	OilPressure         40010
14	BatteryVoltage	
15	Opened              40001:13
16	Closed              40001:14
17	Tripped	
18	Synched             40001:16

Note that the entry in B2 is just the "greater than" and "less than" operators, no = sign or quotes like you would normally use for Advanced Filter criteria. That's the only way to get the filter to recognize blank cells.

Then run this code, which will paste the filtered list starting in Sheet1!E4 (adjust as required)

Sub FilterNoAddress()
   Range("A4:B18").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "A1:B2"), CopyToRange:=Range("E4:F4"), Unique:=False
End Sub

Add some code to copy the Filtered list over to Sheet2 and you're all set.

You could Hide the rows with the Criteria and the Filtered List so that they don't show up on Sheet1.

Report •

Related Solutions

September 21, 2010 at 12:17:22
Thanks DerbyDad! The advice you've given me so far has been extremely helpful!


The MASTER_LIST! will be ~300 rows in length and have ~15 sections in it, each linked to its respective SECONDARY_SHEET!.

I used a 'super macro' so after entering the ~300 addresses on the MASTER_LIST!, I don't have to go back and run a macro for each-and-every SECONDARY_SHEET! I want to produce.
I used the following 'super' macro:

Sub FilterNoAddress_Master()
Range("A4:B18").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:B2"), CopyToRange:=Range("D4:E4"), Unique=False
Range("A25:B33").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A22:B23"), CopyToRange:=Range("D24:E24"), Unique=False
Range("A41:B87").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A38:B39"), CopyToRange:=Range("D41:E41"), Unique=False
Range("A94:B111").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A91:B92"), CopyToRange:=Range("D94:E94"), Unique=False
Range("A118:129").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A115:B116"), CopyToRange:=Range("D118:E118"), Unique=False
Range("A136:B169").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A133:B134"), CopyToRange:=Range("D136:E136"), Unique=False
and so on.......
End Sub

The CopyToRange pushes to Columns D and E because I needed to remove the <> and column title characters before pushing to a SECONDARY_SHEET!. I can only have certain infomation in the .csv file I am trying to produce.




After entering modbus addresses into the MASTER_LIST!, I want to use the MASTER_LIST! 'super macro' to produce lists in the SECONDARY_SHEETs!, (only
where address's are listed inthe MASTER_LIST!). I would like to save each SECONDARY_SHEET! as it's own .csv file.

[.csv File Name Format]: [G5]+SECONDARY_SHEET!.csv

G5 will be a random cell where I type in a specific customer sitename.
The issue is that the .csv files that I currently create for each SECONDARY_SHEET! contain commas (,) where there should be nothing. This is because the IF statements I used leave a formula as a place holder, even if the cell appears blank.

[Example .csv file]

Closed 40001:01
RelayTripped 40001:03

These comma's are not going to allow me to import the .csv files properly.

I appreciate any help. I'm an engineering intern who's been researching this and working on it for a while. This will be a major accomplishment for me as my internship comes to a close.

Report •

September 21, 2010 at 13:23:37
What the heck is a "super macro"?

I won't claim to understand everything you're trying to do because I don't have time to try and decipher it all.

From a quick read I don't quite understand why you are still using IF statements. If you are using a macro to filter the sections to eliminate the blank rows, why don't you just copy the filtered lists to the proper sheets?

What am I missing?

Report •

September 22, 2010 at 09:48:49
somebody told me that a "supermacro" was when you took a whole bunch of lines of code for one macro and lumped them all into anothermacro instead of several smaller ones. shows how much i know about coding....

i cannot just copy the filtered lists to new sheets because the list's length will change from site to site. i really wish i could do that but i have to make this work for dozens of customer sites.

i have figured everything out that i need to do so far with the exception of 1 thing:

When an IF statement returns a "0" meaning an attribute is NOT tagged with an address, I can hide the rows with zeros, but I cannot delete them. If I cannot delete them then my .csv files will have extra zeros in them which won't work...

Column A: =IF(MASTER_LIST!D26<>"",MASTER_LIST!D26,0)

And my code that will 'hide' rows with zeros is:
Sub HideRows()
Dim cell As Range, rng As Range
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(1).SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
For Each cell In rng
If cell.Value = 0 Then
cell.EntireRow.Hidden = True
End If
End Sub
In Set rng = Columns(1) does that mean you can only set the range for column 1? can you set the range for multiple (2) columns?

Thank you!

Report •

September 22, 2010 at 10:30:36
re: "super macros"

As I'm sure you now realize, there is no such thing as a "super macro". Combining lines from various macros into one just produces a "longer" macro that does more stuff, but there is nothing "super" about it.

re: "i cannot just copy the filtered lists to new sheets because the list's length will change from site to site."

Don't be so sure. There are numerous ways to determine the length of a list using VBA and then use that result to determine how large of a range to copy. All you need to do is to tell VBA where to start loooking and then give it some criteria (like a blank cell perhaps) that lets it know that that is the end of the list. You then use that Row number to tell VBA the Range to Copy.

Sub CopyMyVariableList()
 first_list_Row = 5 'This could be a value calculated by VBA
 last_Row = 100 'This could be a value calculated by VBA
'Loop through cells looking for an empty cell
  For num_Row = first_list_Row To last_Row
'When an empty cell is found, record the row number and stop looking
   If Sheets(1).Range("E" & num_Row) = "" Then
    last_list_Row = num_Row
    Exit For
   End If
'Use the first_list_Row and last_list_Row variables to set the Range to copy
   Sheets(1).Range("E" & first_list_Row & ":F" & last_list_Row).Copy _
End Sub

re: "I can hide the rows with zeros, but I cannot delete them. "

Do you mean that you don't know how to delete them via VBA or that you cannot delete them because it will mess up your spreadsheet?

If you don't know how to delete them, go back and read Response # 3 in which I explained how to delete rows based on a given criteria.

If you mean that deleting them will will mess up your spreadsheet, then copy the whole table to another location, delete the Rows with 0's from there and create your .CSV files from that list.

Finally, a comment about your IF statements, assuming you still plan to use them:

Column A: =IF(MASTER_LIST!D26<>"",MASTER_LIST!D26,0)

Why have one that looks for = "" and one that looks for <> ""?

Why not be consistant and use the same structure for both?

Column A: =IF(MASTER_LIST!D26<>"", MASTER_LIST!D26, 0)
Column B: =IF(MASTER_LIST!E26<>"", MASTER_LIST!E26, 0)

It's not a big deal, just easier to understand when you come back and look at the formula a year from now and try to figure out what you were doing.

Report •

September 23, 2010 at 04:18:11
I'm not if this will help, but I just learned something new about filtering data to another sheet:

While you cannot "push" the filtered data to a different sheet via the Filter dialog box, you can "pull" it.

1 - Name the range that you want to filter, e.g. myRange.

2 - On the sheet where you want the filter data to reside, open the Filter dialog box and use =myRange in the "List range" field.

If you can name the ranges that you need to filter the blank rows from, this may be a method of getting them to the other sheets.

Good luck.

Report •

Ask Question