Auto-Sort Macro for Multiple Sheet Workbook.

R

RAF1982

New Member
Joined
Sep 4, 2014
Messages
12
Hello Everyone... I have been working on trying to figure out a specific Macro for the last 5 hours and can't seem to get it right. I have searched this forum and can't find anything that specifically relates to what I am trying to do so any help would be greatly appreciated. Here is what I am looking for:

I have a 5 sheet workbook that I am using to record sales data for a sales region. 4 out of the 5 sheets pull data from 1 specific sheet (the 5th sheet). Those 4 sheets have formulas entered in each cell to pre-populate data from the 5th sheet so there is no manual entry on any of those 4 sheets. Manual entry only occurs on the 5th sheet.

Each of the 4 sheets data range is from A6:M50 and that will never change. What I am looking to do is have each sheet auto-sort in ascending order as data is entered into the 5th sheet. When the data is entered, I only want a specific column to sort per page and each row needs to sort with the column.

The first sheet's data range is A6:M50 with the sort range being F6:F50
The second sheet's data range is A6:M50 with the sort range being G6:G50
The third sheet's data range is A6:M50 with the sort range being K6:K50
The fourth sheet's data range is A6:M50 with the sort range being L6:L50

I attempted the following code and it worked only when I manually entered the data on the sheet the code was entered but it's functionality went away when I entered the formulas to pre-populate the cells with data from the 5th sheet. This is the first time I've tried Macros so I could be doing it completely wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Range("A6:M50").Sort _
Key1:=Range("F6:F50"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If

End Sub

I am guessing I need to have a separate code for each sheet so the correct range is being sorted, right? If so, what would the code be and do I just enter it by selecting view code on the specific tab and pasting it in?

I am using Excel 2010.

Again, any help would be greatly appreciated. Thank you for your time and help.
 
Last edited:
Sort by date Sort by votes
Put this code in "THis workbook" module. It should fire everytime a change is made.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Set ws1 = Worksheets("Sheet1") 'change sheet names to suit.
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Sheet4")
Set rng1 = ws1.Range("A6:M50")
Set rng2 = ws2.Range("A6:M50")
Set rng3 = ws3.Range("A6:M50")
Set rng4 = ws4.Range("A6:M50")
ws1.Sort.SortFields.Clear
   ws1.Sort.SortFields.Add Key:=Range("F6:F50"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ws1.Sort
        .SetRange rng1
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ws2.Sort.SortFields.Clear
   ws2.Sort.SortFields.Add Key:=Range("G6:G50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange rng2
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ws3.Sort.SortFields.Clear
   ws3.Sort.SortFields.Add Key:=Range("K6:K50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws3.Sort
        .SetRange rng3
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ws4.Sort.SortFields.Clear
   ws4.Sort.SortFields.Add Key:=Range("L6:L50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws4.Sort
        .SetRange rng4
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End Sub
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
FarmerScott - you are an Excel Ninja. Works like a charm - thank you so much.

I don't know if this is possible, but is there a way when auto-sorting in ascending order to have everything that equals $0 be moved to the bottom? For example, some accounts will be negative in profit where others will be positive. As each territory owner goes in to enter their data, all the negative auto-sorts to the top and all the positive auto-sorts to the bottom due to the ascending order - this leaves all the unentered territories that equal $0 in the middle. Not a big issue, but if there's a way to fix it that would be great.

Thank you again for all your help. This is going to be awesome to work with.
 
Upvote 0
Thanks for the nice comment, however the core of that code comes from a recorded macro. I just did some tweaking to suit your needs.

The following will hide the rows with 0 (zero) and blank values in the respective columns (F,G,L and K).

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet


Set ws1 = Worksheets("Sheet1") 'change sheet names to suit.
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Sheet4")
Set rng1 = ws1.Range("A6:M50")
Set rng2 = ws2.Range("A6:M50")
Set rng3 = ws3.Range("A6:M50")
Set rng4 = ws4.Range("A6:M50")

rng1.EntireRow.Hidden = False

ws1.Sort.SortFields.Clear
   ws1.Sort.SortFields.Add Key:=Range("F6:F50"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ws1.Sort
        .SetRange rng1
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
For x = 50 To 6 Step -1
    If Cells(x, 6).Value = 0 or Cells(x,6).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x

rng2.EntireRow.Hidden = False
ws2.Sort.SortFields.Clear
   ws2.Sort.SortFields.Add Key:=Range("G6:G50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange rng2
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  For x = 50 To 6 Step -1
    If Cells(x, 7).Value = 0 or Cells(x,7).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x
rng3.EntireRow.Hidden = False
    ws3.Sort.SortFields.Clear
   ws3.Sort.SortFields.Add Key:=Range("K6:K50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws3.Sort
        .SetRange rng3
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    For x = 50 To 6 Step -1
    If Cells(x, 11).Value = 0 or Cells(x,11).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x
rng4.EntireRow.Hidden = False
    ws4.Sort.SortFields.Clear
   ws4.Sort.SortFields.Add Key:=Range("L6:L50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws4.Sort
        .SetRange rng4
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    For x = 50 To 6 Step -1
    If Cells(x, 12).Value = 0 or Cells(x,12).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x
    End Sub
 
Last edited:
Upvote 0
When data is entered with that code running it distorts Sheet 5 by moving the formatting and cells around; Sheet 5 is the sheet data is being entered into so no sorting is needed for that page. It does auto-sort Sheet 1, Sheet 2, Sheet 3, and Sheet 4 but it doesn't move the $0's to the bottom.

It comes up with the following error : Run-time Error "13", Type Mismatch. When I select Debug, it highlights the following code:


If Cells(x, 7).Value = 0 or Cells(x,7).Value="" Then</pre>
 
Upvote 0
OK I have done a little tweak so sheet 5 is not altered.

This code will hide the $0 rows. I will have a look at developing some code to put the $0 at the bottom.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet


Set ws1 = Worksheets("Sheet1") 'change sheet names to suit.
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Sheet4")
Set rng1 = ws1.Range("A6:M50")
Set rng2 = ws2.Range("A6:M50")
Set rng3 = ws3.Range("A6:M50")
Set rng4 = ws4.Range("A6:M50")

rng1.EntireRow.Hidden = False

ws1.Sort.SortFields.Clear
   ws1.Sort.SortFields.Add Key:=Range("F6:F50"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ws1.Sort
        .SetRange rng1
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

ws1.activate
For x = 50 To 6 Step -1
    If Cells(x, 6).Value = 0 or Cells(x,6).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x

rng2.EntireRow.Hidden = False
ws2.Sort.SortFields.Clear
   ws2.Sort.SortFields.Add Key:=Range("G6:G50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange rng2
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  ws2.activate
For x = 50 To 6 Step -1
    If Cells(x, 7).Value = 0 or Cells(x,7).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x


rng3.EntireRow.Hidden = False
    ws3.Sort.SortFields.Clear
   ws3.Sort.SortFields.Add Key:=Range("K6:K50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws3.Sort
        .SetRange rng3
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
   ws3.activate
 For x = 50 To 6 Step -1
    If Cells(x, 11).Value = 0 or Cells(x,11).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x
rng4.EntireRow.Hidden = False
    ws4.Sort.SortFields.Clear
   ws4.Sort.SortFields.Add Key:=Range("L6:L50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws4.Sort
        .SetRange rng4
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ws4.activate
   For x = 50 To 6 Step -1
    If Cells(x, 12).Value = 0 or Cells(x,12).Value="" Then
    Rows(x).EntireRow.Hidden = True
    End If
Next x
    End Sub
 
Upvote 0
This code will put the $0 values at the bottom of the list.
Put this code in a normal module and give it a test run.
It will only run on Sheet1.
If it is Ok we can incorporate it into the larger code.

Code: 
Sub Sort_Zero_cells_last()
' this code colours 0 (zero) value cells vbRed and then custom sorts
'so they are sorted last. vbred is removed.

Dim ws1 As Worksheet
Dim lr As Long
Dim rng As Range
Set ws1 = Worksheets("Sheet1")
lr = ws1.Cells(Rows.Count, "F").End(xlUp).Row
Set rng = ws1.Range("F1:F" & lr)
ws1.Activate
For x = 6 To 50
If Cells(x, 6).Value = 0 Then
        Cells(x, 6).Interior.Color = vbRed
        End If
Next x

    ws1.Sort.SortFields.Clear
    ws1.Sort.SortFields.Add Key:=Range("F6:F50"), _
        SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
    With ws1.Sort
        .SetRange Range("A6:M50")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

For x = 6 To 50
If Cells(x, 6).Interior.Color = vbRed Then
        Cells(x, 6).Interior.Pattern = xlNone
        End If
Next x
End Sub
 
Upvote 0
The code you created to hide the $0 rows works perfectly, no more Run-Time Error 13. This is exactly what I was looking for on this workbook, thank you so much for your help.

Quick Question:

I am creating a separate workbook (different from the one we've been discussing) that is setup the exact same way, just a different data range. All data will be entered in Sheet 5 and auto populate into Sheet 1, 2, 3, and 4 like the other one. The data range for this workbook is A6:L155. The auto-sort columns are E6:E155, F6:F155, J6:J155, and K6:K155.

I tried entering the same code but changed the data range and it's getting the same Run-Time Error 13. When I go to debug it highlights the following code: If Cells(x, 7).Value = 0 or Cells(x,7).Value="" Then

Below is the code I entered. Do you know what needs to be modified to fix the run-time error?

Thanks again for all your help... You are awesome!!!

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]-->
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Dim rng1 As Range
  Dim rng2 As Range
  Dim rng3 As Range
  Dim rng4 As Range
  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim ws3 As Worksheet
  Dim ws4 As Worksheet
   
   
  Set ws1 = Worksheets("Monthly Profit")
  Set ws2 = Worksheets("Monthly Profit YOY")
  Set ws3 = Worksheets("Yearly Profit")
  Set ws4 = Worksheets("Yearly Profit YOY")
  Set rng1 = ws1.Range("A6:L155")
  Set rng2 = ws2.Range("A6:L155")
  Set rng3 = ws3.Range("A6:L155")
  Set rng4 = ws4.Range("A6:L155")
   
  rng1.EntireRow.Hidden = False
   
  ws1.Sort.SortFields.Clear
     ws1.Sort.SortFields.Add Key:=Range("E6:E155"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortTextAsNumbers
      With ws1.Sort
          .SetRange rng1
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
   
  ws1.Activate
  For x = 155 To 6 Step -1
      If Cells(x, 6).Value = 0 Or Cells(x, 6).Value = "" Then
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng2.EntireRow.Hidden = False
  ws2.Sort.SortFields.Clear
     ws2.Sort.SortFields.Add Key:=Range("F6:F155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws2.Sort
          .SetRange rng2
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
    ws2.Activate
  For x = 155 To 6 Step -1
      If Cells(x, 7).Value = 0 Or Cells(x, 7).Value = "" Then
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng3.EntireRow.Hidden = False
      ws3.Sort.SortFields.Clear
     ws3.Sort.SortFields.Add Key:=Range("J6:J155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws3.Sort
          .SetRange rng3
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
     ws3.Activate
   For x = 155 To 6 Step -1
      If Cells(x, 11).Value = 0 Or Cells(x, 11).Value = "" Then
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng4.EntireRow.Hidden = False
      ws4.Sort.SortFields.Clear
     ws4.Sort.SortFields.Add Key:=Range("K6:K155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws4.Sort
          .SetRange rng4
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
      ws4.Activate
     For x = 155 To 6 Step -1
      If Cells(x, 12).Value = 0 Or Cells(x, 12).Value = "" Then
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
      End Sub
 
Last edited:
Upvote 0
RAF1982,

Sorry but I am a little lost as to the erroring. It looks like you have made all the right adjustments.

Can you check-

1. that you have put the code in the "This Worksheet" module of your new workbook.
2. the tab names of the sheets match the declaratations of the code. (it should be OK as your code would have errored earlier.)
3. do you have any merged cells in Col F of Sheets("Monthly Profit YOY")?
4 Col F is not hidden?
5. did the code make the correct changes to Sheets("Monthly Profit"), before it errored on Sheets("Monthly Profit YOY")?

thanks.
 
Upvote 0
I figured it out. Here's the code that is working:

Pretty much everything on the spreadsheet stayed the same but I removed one column which changed the data range from A6:M50 (Original Spreadsheet) to A6:L155 (New Spreadsheet). Because of this I needed to change the section below in red. Works like a charm now and I wouldn't have been able to make it happen without your original code, so thank you.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Dim rng1 As Range
  Dim rng2 As Range
  Dim rng3 As Range
  Dim rng4 As Range
  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim ws3 As Worksheet
  Dim ws4 As Worksheet
   
   
  Set ws1 = Worksheets("Monthly Profit")
  Set ws2 = Worksheets("Monthly Profit YOY")
  Set ws3 = Worksheets("Yearly Profit")
  Set ws4 = Worksheets("Yearly Profit YOY")
  Set rng1 = ws1.Range("A6:L155")
  Set rng2 = ws2.Range("A6:L155")
  Set rng3 = ws3.Range("A6:L155")
  Set rng4 = ws4.Range("A6:L155")
   
  rng1.EntireRow.Hidden = False
   
  ws1.Sort.SortFields.Clear
     ws1.Sort.SortFields.Add Key:=Range("E6:E155"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortTextAsNumbers
      With ws1.Sort
          .SetRange rng1
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
   
  ws1.Activate
  For x = 155 To 6 Step -1
      [COLOR=#ff0000]If Cells(x, 5).Value = 0 Or Cells(x, 5).Value = "" Then[/COLOR]
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng2.EntireRow.Hidden = False
  ws2.Sort.SortFields.Clear
     ws2.Sort.SortFields.Add Key:=Range("F6:F155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws2.Sort
          .SetRange rng2
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
    ws2.Activate
  For x = 155 To 6 Step -1
      [COLOR=#ff0000]If Cells(x, 6).Value = 0 Or Cells(x, 6).Value = "" Then[/COLOR]
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng3.EntireRow.Hidden = False
      ws3.Sort.SortFields.Clear
     ws3.Sort.SortFields.Add Key:=Range("J6:J155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws3.Sort
          .SetRange rng3
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
     ws3.Activate
   For x = 155 To 6 Step -1
      [COLOR=#ff0000]If Cells(x, 10).Value = 0 Or Cells(x, 10).Value = "" Then[/COLOR]
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
   
  rng4.EntireRow.Hidden = False
      ws4.Sort.SortFields.Clear
     ws4.Sort.SortFields.Add Key:=Range("K6:K155") _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ws4.Sort
          .SetRange rng4
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      
      ws4.Activate
     For x = 155 To 6 Step -1
    [COLOR=#ff0000]  If Cells(x, 11).Value = 0 Or Cells(x, 11).Value = "" Then[/COLOR]
      Rows(x).EntireRow.Hidden = True
      End If
  Next x
      End Sub
 
Upvote 0
So now that I have the code working, how do I protect the sheets? When I protect the sheets and enter data it says: Run-Time Error '1004' - Unable to set the hidden property of range class. When I hit debug, it shows: rng.EntireRow.Hidden = False.

I am not trying to hide the formulas, I just need to protect the cells so information isn't submitted incorrectly.

Anything that can fix this for both workbooks? I appreciate your help - thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
data extract between sheets For the value entered
Replies
1
Views
142
LazyBug
LazyBug
H
  • Question
How to create a macro to sort 2 non-adjacent columns?
Replies
1
Views
143
helpneeded2
H
B
  • Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
805
mumps
mumps
J
  • Question
No formulas working to rename sheets within workbook
Replies
3
Views
94
Joe4
Joe4
L
  • Solved
VBA code checks a condition, stops macro if FALSE, etc.
Replies
3
Views
135
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,804
Messages
6,121,652
Members
449,045
Latest member
Marcus05

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top