Further to deleting identical rows

Audiojoe

Active Member
Joined
Feb 20, 2002
Messages
285
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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top