How to Remove Hidden rows in excel

As an auditor i have to work with excel , and some clients are just using excel for book keeping purpose, the way in which they use excel for book keeping is some time totally different than what a professional accountant’s work.

As we need data in categorical form most of times, so Filter option is used by many of us to categorize data, however it is very annoying to use sum function in this case , or hidden rows can be a annoying thing itself.

When i encounter such thing i was out of clue at start, but than i tried to search a reason, that how can i copy or move all cells shown by a filter. so questions i asked to me were:

How to copy rows shown by filter?

How to copy only visible rows?

and when i dig t further i found out that proper way will be to:

Remove Hidden rows in excel.

Now the question is

How to Remove Hidden rows in excel

So now i am sharing a very easy approach to remove all hidden rows , This will work if they are hidden due to filters or you hide them manually, it does not matter. Following steps are needed to perform in order to Remove Hidden rows in excel:

1. Developer options should be enabled in order to use this procedure.Click here for tutorial on how to enable developer options.
2. Press “Alt and F11” simultaneously.
3. A new screen will appear just like below mentioned screen.
4. If these lines are not showing or a blank grey background appears, Than click on tab named Run as shown in upper menu of this picture.
5. Click on Run Sub/user form as shown in image.
6. Type name of macro and click on Create button.
   
7. A new screen will appear, Type following code in that screen, Just delete the default text appears on that screen. 
   Sub RemoveHiddenRows()
Dim xRow As Range
Dim xRg As Range
Dim xRows As Range
On Error Resume Next
Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
If xRows Is Nothing Then Exit Sub
For Each xRow In xRows.Columns(1).Cells
If xRow.EntireRow.Hidden Then
If xRg Is Nothing Then
Set xRg = xRow
Else
Set xRg = Union(xRg, xRow)
End If
End If
Next
If Not xRg Is Nothing Then
MsgBox xRg.Count & " hidden rows have been deleted", , "Waseem Anwar Code"
xRg.EntireRow.Delete
Else
MsgBox "No hidden rows found", , "Waseem Anwar Code"
End If
End Sub
   you will see a screen like this now
8. Now press f5 button, Use it with fn if you have function key in keyboard.
9. You will see a prompt like this. And as shown by image we had two hidden rows and both are deleted now.
10. Now you can close developer option, and can go back to your excel sheet.