Excluding row from list

Hi Johnmus,

We really need a sample of this problem to get close to solving it. Click on the Go Advanced button below the message area and then on the Paper Clip Icon above the message area to attach a file.

I have found a macro to make this work

Option Explicit

Sub CheckColumnB()
Dim LastRow As Long, i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

    For i = LastRow To 1 Step -1
        If Cells(i, "B").Value = " " Then Rows(i).Delete (xlShiftUp)
    Next i

Application.ScreenUpdating = True
End Sub

However I want to apply the maxcro to three different sheets. Sheet2, Sheet3, and Sheet4. How would I accomplish this?

Also is there a way to start the macro when opening the worksheet?

Should I have presented this to the VBA forum>

Thanks

Hi John,

First - please edit your last message (the Green Edit under the message). Click on the "Go Advanced" under the message box, select all the code and click on the # icon above the message area. Then save the changes. This will put code tags around your code and will make the forum moderators happy.

Your code will work on the active worksheet, so it will work on the sheet you are viewing.
You can make it work whenever you "Activate" that worksheet by putting this code behind each of the worksheets you want it to trigger on.

Private Sub Worksheet_Activate()
    Call CheckColumnB
End Sub

hope this helps

Hi,

I hope you can help me solve this problem. I have a competition spreadsheet with a event info form where I list the competitors. This changes from week to week, so the list in the Event form is blank until we know who is competing. I am using this macro to hide to unnessary rows and also use an unhide macro just in case we need to make adjustments.

What I want to accomplish is that when I open the event info sheet and type in the names, the empty rows on the next sheets - there are three sheets (sheet1,sheet2,sheet3) - are hidden once I acces those sheets to put in the numbers.

Here is the code

Option Explicit

Sub HideRows()

Application.ScreenUpdating = False

Dim cell As Range
    For Each cell In Range("B4:B81")
        If cell.Value = " " Or cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        Else
            cell.EntireRow.Hidden = False
        End If
        Next

Application.ScreenUpdating = True
     
End Sub


Sub UnHideRows()

    Application.ScreenUpdating = False

    Range("B4:B81").EntireRow.Hidden = False
        
    Application.ScreenUpdating = True

End Sub

This works but I have to access each sheet to run the macro. I tried adding the activate code above but that did not work.

Thanks for any help!

Hi John,

I believe there is some confusion between Code Behind a Sheet and Code In A Module.
The code behind a sheet is event code that works on that individual sheet. Code in a module can work on any sheet. I believe you need your above code in a module. Then while looking at a sheet (this would be your activesheet) you run the above code (from a module). The code will then work on the active sheet.

I believe this is the problem. If you could attach a sample workbook we could better understand where you code lives and why it isn't acting like you expect.

Hi,

I've got everything working really well with the following Code

Option Explicit

Sub HideRows()

ActiveSheet.Unprotect

Application.ScreenUpdating = False

Dim cell As Range
    For Each cell In Range("B4:B42")
        If cell.Value = " " Or cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        Else
            cell.EntireRow.Hidden = False
                    
        End If
        Next
ActiveSheet.Protect
Application.ScreenUpdating = True
     
End Sub
Sub UnHideRows()
ActiveSheet.Unprotect
    Application.ScreenUpdating = False

    Range("B4:B42").EntireRow.Hidden = False
        
    Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

Now when I type the names of the competitors in the event sheet all empty rows will be hidden. The macro activates automatically but I've assigned Ctrl h to execute the hiding function and Ctrl u to execute the unhiding function. The sheet will also protect and unprotect with the macro.
I am just beginning with VBA and the problem I need to solve is the awards sheet will show #NUM! or #N/A or 0.00 because some formulas are responsing to 0 for not used entries.I would like to be able to also hide these upon activating the sheet.

On the Awards sheet I also cannot hide with the shortcut Ctrl h - Unhide, Ctrl u works fine

I am attaching a shortened version of the workbook.

Thanks for your help.

Hi Johnmus,

Ctrl-H is reserved for the Find and Replace dialog box. See http://office.microsoft.com/en-gb/ex...001111659.aspx

I'm concerned that you have your code in the ThisWorkbook. You should INSERT a MODULE and put most of your code in it. Read the first sentence of http://www.cpearson.com/excel/codemods.htm