General discussion


Excel quirk

By brifd1 ·
Excel version 2002, no updates needed (per the MS website), didn't see a Knowledgebase article on this. The situation: I have filtered certain rows and I want to delete these rows using a Visual Basic macro. I'm not up to speed on VB to the extent of being able to write a macro like the "good old days" of Excel 1, 2, 3, and 4, so what I do is record a macro that does this: group (the second time, I tried hiding the rows, with the same end result) the heading/title lines 1-2-3-4 in my spreadsheet, select all, use F5 to select the visible cells only, delete rows. From here, I want to see all of the remaining rows, including the header row. I can't get there, whether by ungroup or unhide or changing the row height, the 1st visible row is still 300-something. Is there any way to get back to (literally) square (A) 1??? Thanks!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Delfeye In reply to Excel quirk

Two things:

1) Here is piece of code that will remove your empty rows.

Sub DeleteEmptyRows()
Dim lastRow As Long
Dim r As Long
lastRow = ActiveSheet.UsedRange.Row - 1 + _
Application.ScreenUpdating = False
For r = lastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

2) Code for returning to A1


Collapse -

by brifd1 In reply to Excel quirk

The above is not too clear. I'll try again:
Rows 1 thru 4: header info. Rows 5+: data, some of which I want to delete. What I had tried:
filter the data to delete, hide rows 1 thru 4, select only visible data, delete rows, attempt to unhide rows 1 thru 4 and show all remaining records. What happens: rows 1 thru 4 don't unhide, setting their row height doesn't work, removing the filter doesn't work.

Related Discussions

Related Forums