asked on

Expand macro to include freezing of top row.

I had this question after viewing Delete empty rows and columns.

I thought I realized what this did and had labeled it accordingly, but I was confused when I ran the macro.

It came up asking for the range which was good, but then asked for Text. Since I wanted it to delete empty rows and columns, the text box should have been more descriptive. I did not put anything in the text box and it did not delete empty columns.

What should I have done here?

If possible, it should also take the step of checking to see if any filters are applied and remove those filters, along with freezing the top row by default.

Sub DeleteRowAndColsNoInclude()

'This function should allow removal of rows that do not include a specified text.
'Update20140618

Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel "
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
    Set xRow = WorkRng.Rows(i)
    Set rng = xRow.Find(xStr, LookIn:=xlValues)
    If rng Is Nothing Then
       xRow.Delete
    End If
Next
For i = WorkRng.Columns.Count To 1 Step -1
    Set xCol = WorkRng.Columns(i)
    Set rng = xCol.Find(xStr, LookIn:=xlValues)
    If rng Is Nothing Then
       xCol.Delete
    End If
Next
Application.ScreenUpdating = True
End Sub

Open in new window

Bill Prew

It looks like the text box was prompting you for text to exclude from the delete. So if you entered ABCDEF then any row or column that had cell with that value would not be deleted.

»bp

Ted Penner

ASKER

Can we make it eliminate that text question and just delete all empty columns and rows?

Bill Prew

Please clarify what constitutes an empty row, and an empty column? For example, if you have a header row, would you never consider it empty, or would it be empty if everything but the header row for that column was empty?

The following should address the other two additions you mentioned:

' Freeze top row
ActiveWindow.SplitColumn = 0
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True

' Remove any auto filter
If ActiveSheet.AutoFilterMode Then ActiveSheet.UsedRange.AutoFilter

Open in new window

»bp

Ted Penner

ASKER

It would be empty if everything but the header row for that column was empty.

On which line would I insert the code?

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial