Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I just downloaded a Micosoft CSV file from Google Contacts and I would like to modify it so that I can organize the contacts in my phone. I am going back to my "dumb" phone as part of an experiment of mine to simplify my life and the "dumb" phone doesn't have room for all the contacts in the file.

With that said, I think Google is, erm, stupid for listing almost everybody I've ever emailed in the life of my Gmail account and others I haven't (I suppose these come from email lists that I'm on). So the end result is that I have a huge list of contacts, but most of them I hardly even know and I don't have a phone number for. I don't send emails on my phone (and I obviously won't be sending them on my flip-phone either), so I don't need them.

So I've imported the CSV file into excel. Basically what I want to do is delete an entire row if, for that row, there is no data in the columns I'm concerned about

R,S,T,U,V,W,AL,AM,AN,AO,AP,BG,BH,BR,BS,BU,BV

So let me simplify the problem a bit. Suppose First, middle and last name are in columns A,B, and C respectively. Then Mobile Phone number is in column D, home phone number is in column E, and email is in column F. For every contact in the list, I want to delete its row if it does not have data in column D or E or BOTH, but I couldn't care less if it has data in column F.

I've written the following macro, but it just seems to be deleting my entire spreadsheet, which it shouldn't be doing. I'm very new to Excel Macros and VBA, this is the first time I've ever done anything with them, so I'd appreciate some help.

Sub deleteBlankRows()
On Error Resume Next
Columns("R:R").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("S:S").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("T:T").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("U:U").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("V:V").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("W:W").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("AL:AL").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("AM:AM").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("AN:AN").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("AO:AO").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("AP:AP").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BG:BG").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BH:BH").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BR:BR").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BS:BS").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BU:BU").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("BV:BV").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
share|improve this question

1 Answer 1

up vote 1 down vote accepted

I think your idea is a good one, it's just not how VBA wants to work. I'd try it with a loop like this -

Sub deleterows()

Dim intcount As Integer
Dim introw As Integer

intcount = ActiveSheet.UsedRange.Rows.Count

For introw = intcount To 1 Step -1
    If Range("B" & introw) = "" Or Range("C" & introw) = "" Then
    Range("B" & introw).EntireRow.Delete
    End If

Next

End Sub

On the if condition, just define whatever columns you want to check as Range("X" & introw) = "" separated by Or.

share|improve this answer
    
Thank you! I just modified it to suit my long list of columns and I changed Or to And because with Or it deleted my entire sheet again. And ensures that all of the columns of interest are blank before deleting while Or only cares if one of them are blank (All my rows had at least one blank column, so it deleted my entire sheet). But again, Thank you!, Thank you!, Thank you! –  audiFanatic Feb 16 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.