Software

Ask something
Discussion Locked

Excel quirk

0 helpful
2 replies

by brifd1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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!

2 replies

by delfeye

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 + _
ActiveSheet.UsedRange.Rows.Count
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

Range("A1").Select

by brifd1

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.

This discussion is closed to new replies.