Need to Delete Rows Automatically 2

[Quintios]

I have a simple spreadsheet that contains information in rows. Because the information is copied from a template-sheet there are some blank rows. If the first cell (in the A column) is empty, then it is for sure a blank row. How can I step through the first column ONLY and delete the blank rows using VBA?

I know this should be fairly easy, but I'm not too good with the VBA stuff... :-(

Thanks in advance!

Onwards,

Q-

 

[Guest_imported]

'First you select the entire row
'You can use a variable, instead of "1:1", to select ANY row
Rows("1:1&quot.Select
'Then, you delete all
Selection.Delete Shift:=xlUp

 

[Quintios]

No no no. Look at the second post below this one with the same title. I hit the 'submit' button before I was able to explain everything a bit better.

I need to be able to step through the data... Like I said, look at the second post.

Thanks!
Onwards,

Q-

 

[DaleWatson123321]

Quintios,

If your process is an "ongoing one" - where you'll need to repeat the copying of data from the template, then I would suggest you seriously consider using Excel's powerful database functions.

A short time ago, I responded to another similar Tek-Tips posting, where the poster had tried out a VBA routine like the one you posted - i.e. where the routine checks the data for blank rows - cell by cell by cell.

I also tested the proposed solution, and "gave up" after waiting 37 minutes for it to delete blank rows for a database populated down to row 65,535.

My option, using Excel database "extraction" function - an extension of Data - Filter - Advanced Filter - took EXACTLY TWO SECONDS - Yes "2 seconds" - for extracting non-blank rows from this 65,535-row database.

If you would like to consider this option, or if you just want to learn more about the capabilities of Excel's "database functionality", email me, and I'll send you the same file which impressed ("blew away&quot the last guy.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Quintios, Because of the mixup, I thought it best that we "tack on" your other posting to this one...

from Quintios' other posting...

I have a simple spreadsheet that contains information in rows. Because the information is copied from a template-sheet there are some blank rows. If the first cell (in the A column) is empty, then it is for sure a blank row. There can be several blank rows in a row, however. How can I step through the first column ONLY and delete the blank rows using VBA?

I found this code for selecting the used area, first to last, in a column:


Option Explicit
Public Sub SelectFirstToLastInColumn()
Dim topcell As Range
Dim bottomcell As Range

Set topcell = Cells(1, ActiveCell.Column)
Set bottomcell = Cells(16384, ActiveCell.Column)
If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)
If topcell.Row = 16384 And bottomcell.Row = 1 Then ActiveCell.Select Else Range(topcell, bottomcell).Select

End Sub


And what I was going to do was to set it as a range and then do a 'for each cell in range' type thing, but I can't get from the code above to setting it as a range.

I know this should be fairly easy, but I'm not too good with the VBA stuff...

Thanks in advance!


Onwards,

Q-

 

[Quintios]

What I ended up doing was, after selecting the range of cells from first to last in the first column, doing a search for a blank cell. I then deleted the row and using a 'for each varCell in Selection' repeated the loop.

Quite nifty, actually.

Email me if you want the code.

quintios@yahoo.com

Onwards,

Q-

 

[MRussell]

When I get blank rows in an Excel list I always try to SORT them out instead of deleting. For the list described above - sort by any column that has a blank (and the blank rows will fall to the bottom of the list) and then use the ordering column as the 2nd sort. It would be possible to record a macro and attach it to a toolbar button to perform the sort as needed.

 

[Quintios]

While I enjoy learning VBA and playing with code, your solution would have been MUCH MUCH faster to implement than the contorted way I went about doing it. When I get the gumption I'm going to change my code to do that.

Someone here at work suggested the same thing yesterday and I kinda smacked my forehead (Oh I coulda had a V-8)...

Onwards,

Q-