Further to deleting identical rows

[Audiojoe]

I would also like something like this but mine would be in macro form. When I push the button I would like to delete all blank rows that are between lines of data.

I'll give you an example:

On some of the spreadsheets we have here, the users input rows of info, but when they start a new day they leave a row or two as a gap to signify a new day (makes it easier for quality checkers). I'd like to have a macro that removes these blank rows

Any suggestions?

 

[dognz]

Assuming that you will always have data in column A, the following code should work for you.

Sub del_row()

Range("a1").Select

Do Until ActiveCell.Row = Range("a65536").End(xlUp).Row + 1

If ActiveCell.Value = "" Then

ActiveCell.EntireRow.Delete (xlUp)

Else

ActiveCell.Offset(1, 0).Select

End If

Loop


End Sub

 

[Guest]

On 2002-02-27 06:30, dognz wrote:
Assuming that you will always have data in column A, the following code should work for you.

Sub del_row()

Range("a1").Select

Do Until ActiveCell.Row = Range("a65536").End(xlUp).Row + 1

If ActiveCell.Value = "" Then

ActiveCell.EntireRow.Delete (xlUp)

Else

ActiveCell.Offset(1, 0).Select

End If

Loop


End Sub

This could result in some rows being skipped.

Better to use :-

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Audiojoe]

OK I just tried that, unfortunately it deleted loads of rows that had info in them too. Some of the rows have a couple of things in them (IE: A1 contains a name and nothing else in the row, whereas A2 contains info in every cell up until X2)

It's just the actual blank rows with nothing in any cell in all the row I want to get shot of.

Oh and could it start at say row A4?
I dont want much do I?

 

[dognz]

YEP - A LOT BETTER!

 

[Guest]

On 2002-02-27 06:37, Audiojoe wrote:
OK I just tried that, unfortunately it deleted loads of rows that had info in them too. Some of the rows have a couple of things in them (IE: A1 contains a name and nothing else in the row, whereas A2 contains info in every cell up until X2)

It's just the actual blank rows with nothing in any cell in all the row I want to get shot of.

Oh and could it start at say row A4?
I dont want much do I?

Do you have any column where the cells always contain data except for the rows that are entirely blank?

 

[Audiojoe]

Yes, there's a date column in column A that everyone fills out

 

[Juan Pablo González]

Follow this steps and RECORD a macro while you are doing them.

Hit F5
Type A65536.
Hit Control Up
Hit Control + and Choose "Entire Column"
Press Shift Control Up
Keep Shift Control Pressed and hit the Down arrow until you are in A4.
Hit Enter
Type this formula

=IF(COUNTA(B4:IV4),1,"")

Hit Control Enter

Hit F5
Click "Special"
Select "Formulas" and leave only checked "Text"
Hit Control -
Select "Entire Row"
Hit Control -
Select "Entire Column"

Stop recording the macro.

How's that ?

 

[Audiojoe]

Afraid not, no. It came up with a message saying it could not shift non-blank data??
You see I have set up a macro that copies and pastes all the agents sheets and into one big worksheet and that's the one I wanted to clean up. Some of the cells the agent fills in are validated, I dont know if this has anything to do with it?

If it's not possible then dont worry too much

 

[Juan Pablo González]

Mhmm, maybe you have some merged cells ?