none
Visual basic and Microsoft Excel question RRS feed

  • Question

  • Hey guys,

    Firstly, I would like to appologize if this is in the wrong area, feel free to move it!

    So I am having a bit of a problem, I have not used VB in a couple of years and I am having trouble trying to get this little macro to work.

    Firstly credit to Sooner Trailer who developed this macro is 1999. I have found much use for this macro, this macro hides all rows that are not active. The reason for this is because I have very long excel sheets and may only edit a row or two, when I run the macro is hides all the cells that are inactive so I can print my sheet without 400 extra papers that I don't need.

    Problem I ran into is, it also hides some of the protected cells I need, not within the range. Meaning those cells are never edited, but still contain vital information.

    Here is the code

    Sub Order()
        ActiveSheet.Unprotect
        Dim J As Integer
        For J = 5 To 557
        Range("F" & J).Select
        If ActiveCell > 0 Then
            Else: Selection.EntireRow.Hidden = True
        End If
        Next J
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End Sub

    The range is F, most of the data I edit is there. However there are some in lets say M that contains a total, or reminders etc. They are hidden after the macro runs.

    So what I am trying to do, is achieve the same functionality, except, have the macro skip over any protected cells. If anyone could help me, that would be most apprciated! Thank you!


    • Edited by LTstutcov Tuesday, May 29, 2012 2:36 PM
    • Moved by Youen Zen Wednesday, May 30, 2012 7:18 AM Not VB issue (From:Visual Basic General)
    Tuesday, May 29, 2012 2:35 PM

All replies

  • Specify in which column the protecttion feature is enable.Also remember that by Default all cell is locked.Hope you are setting the locked property only when required.

    The below will do the job but will take lot of time beacuse it will search all column if any F column cell is blank.

    Sub Order()

        Dim j As Long
        Dim rng As Range, rAll As Range
       
        ActiveSheet.Unprotect
       
        'It is safe to use long always
       
        For j = 5 To 557
            If Not (Range("F" & j)) Then
             
                For Each rng In Range(j & ":" & j)

                    If rng.Locked Then
                        Exit For
                    End If
                   Range("F" & j).EntireRow.Hidden = True
                Next rng
               
            End If
        Next j
       
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    End Sub

    Wednesday, May 30, 2012 8:06 AM
    Answerer
  • **Edit**

    Seems like this thread was moved, it is now in the correct section, disregaurd that below.

    Well I now understand that this was the wrong forum to post in, however 2 out of the three links provided are no longer active. The last link lead to a forum that was dead. I have to give credit where credit is due, Learning and Learning took the time to help me, even though under normal circumstances I was not supposed to post here.

    For that, I appreciate the help very much so. I have tried compiling your code LaL but the debug points at the line

    If Not (Range("F" & j)) Then

    and tells me "Run Time error 13 - Type mismatch"

    I will continue to play around with this code. However, I learned a neat trick the other day that showed me how to lock certain cells, and not the whole page. Simply highlight every cell and go into format cell, make sure under the properties where you can select if its locked or not that everything is unlocked. Then simply select any cell that you want to lock and highlight it, then just go back into format cell and lock them. So all the data we need to edit is in unlocked cells normally in the range of F. Other cells may contain words not in the F cell range but are locked because we need them. I hope that clears some stuff up. Any more help is much appreciated.

    **Edit 2**

    To clarify and hopefully reduce headache's I will show you an example. In my long excel file I have headers above other cells to specify what they are. For instance.

       E            F

    DOGS:
    Dog1       10
    Dog2       2
    Dog3       1

    CATS:
    Cat1        1
    Cat2        4
    Cat3        6

    As we can see, the values I would edit are in the F row. However, the "DOGS:" is like a header, and is not in the F range, but I locked them hoping that the macro was not deleting them. It turns out the macro does just that because there is nothing in "DOGS:" F row. Hope that clears some stuff up.


    • Edited by LTstutcov Wednesday, May 30, 2012 2:11 PM
    Wednesday, May 30, 2012 1:53 PM
  • See if it helps.I have only assumed that if E column has locked property true then you need not hide even F column may be blank for that row.

    Sub Order()

        Dim j As Long
        Dim rng As Range, rAll As Range
       
        ActiveSheet.Unprotect
        'It is safe to use long always
       
        Range("f5:f557").EntireRow.Hidden = True
       
        For j = 5 To 557
                If Range("F" & j).Value Or Range("e" & j).Locked Then
                 Range("F" & j).EntireRow.Hidden = False
            End If
        Next j
       
        ActiveSheet.Protect DrawingObjects:=True, _
            Contents:=True, Scenarios:=True

    End Sub

    Wednesday, May 30, 2012 2:43 PM
    Answerer
  • Hey Learning and Learning,

    I tried your new code and I seem to be getting better results, except it still deletes some of the locked cells and does not show the contents in the F range. Would you like the excel worksheet I am working off of? I don't want you to waste your time as I am probably leading you blindly.

    http://www.mediafire.com/file/d3ri7bb4z3woiva/new.xls

    I uploaded it to media fire for you. Upon opening you will see there are a bunch of Bold words, these bold words are areas I locked, because they specifiy what data I am editing. The non-bold words are data that I have to enter, and what I am trying to achieve is hiding the un-bold words that do not have anything in them, so I don't have to print a billion pages. Hope this also helps, and thanks again!!

    Wednesday, May 30, 2012 3:40 PM
  • It is nice that you uploaded sample.Otherwise I could have been searching still.The problem was that you have merged the cells in E & F columns.And when merged, the first cell contains the data which is actually in E column not F column.

    Suppose in A1:B1 you have merged then A1 will conatin the value and B1 will be empty.

    Sub Order()

        Dim j As Long
        Dim rng As Range, rAll As Range
       
        ActiveSheet.Unprotect
        'It is safe to use long always
       
        Range("f5:f557").EntireRow.Hidden = True
       
        For j = 5 To 557
                If Range("e" & j).Value <> "" Or Range("A" & j).Locked Then
                 Range("F" & j).EntireRow.Hidden = False
                 Range("F" & j).EntireRow.Interior.ColorIndex = 7
                 'remove above line when satisfied with this sub.
            End If
        Next j
       
        ActiveSheet.Protect DrawingObjects:=True, _
            Contents:=True, Scenarios:=True

    End Sub

    Thursday, May 31, 2012 7:53 AM
    Answerer