Need help with VBA code. Tons of code seperated in two, second part of code doesn't work.

I had to break up the 2 sections of code because it was to long.
The left column on a sheet will run the first part of the code.
And the right column on that same sheet will run the second set.
I'm not sure what the second part should be at the very beginning.

You'll notice a line across the whole document where the second part of the code starts.
I've tried changing the name of the second part of the code, but it doesn't work.
If I delete the first part of the code, then the second one works fine.

It seems to me your first 750 lines could be reduced to:

Please Login or Register  to view this content.

I'll leave it to you to simplify the rest of your code and incorporate it into the above sub.

I'm not sure what all that code means (.Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123)), but looks very interesting, and I guess saves a ton of editing.

Thanks, and I'll look at it soon.

O.K. I think I figured it out.
So basically if cell E4 is empty, then look at row 4, then the 41'st row after that, etc.
And I guess columns would be something like:

If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
blHide = (Target.Value = ""): Cw = Target.Column
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Cw & ":" & Cw & "," & Cw + 41 & ":" & Cw + 41 & "," & _
Cw + 82 & ":" & Cw + 82 & "," & Cw + 123 & ":" & Cw + 123).EntireColumn.Hidden = blHide
.Protect (sPW)
End With

Originally Posted by FragaGeddon

I'm not sure what all that code means

Have a look at what Rw is and how it is derived. The rest is just math & string compilation.

Originally Posted by FragaGeddon

And I guess columns would be something like:

If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
blHide = (Target.Value = ""): Cw = Target.Column
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Cw & ":" & Cw & "," & Cw + 41 & ":" & Cw + 41 & "," & _
Cw + 82 & ":" & Cw + 82 & "," & Cw + 123 & ":" & Cw + 123).EntireColumn.Hidden = blHide
.Protect (sPW)
End With

Correct, though you should also define Cw As Long (Since Rw is an abbreviation of Row, I wouldn't abbreviate Column as Cw).

PS: Please use the code tags, inserted via the # symbol on the posting menu, when posting code.

Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.

Please Login or Register  to view this content.

Originally Posted by FragaGeddon

Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.

What are you trying to hide/unhide? Yoy have defined 'Cl = Target.Column', but that just returns a number thatou need to convert that to a letter. But, if you do that and reference Cl for 'Hidden = blHide', what you'll end up hiding is column I. Is that your intention?

Is it only I4 you're concerned with for the column hiding/unhiding? Your code presently tests I4:I33, so any of those cells will trigger that.

So why is your added code trying to hide columns on sheets "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13" & "Extra Week", but not on the sheet "Daily Sales Tracking"?

Sorry I was just testing to see if it worked. For the "Daily Sales Tracking" sheet, this is what needs to be done:

On the "Input Info" sheet, cells I4 to I33 it would have a name or have nothing, basically the same as E4 to E33.
Except I need it to hide 2 rows & 2 columns at the same time on the "Daily Tracking Sheet". There's already 4 columns hidden between each of the 2 columns that need to be displayed, or hidden.

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

Note that I've revised some of the earlier code to make it easier for people to understand and, hence, maintain.

Originally Posted by FragaGeddon

The only thing I get confused about is when you do for the "CAST" sheet: .Rows((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19)
I'm guessing your saying start at row 0 (Rw - 4), then hide the next 19 rows, then add 1 blank row(?), and the second part would start at row 1 and hide the next 19 rows?

If Rw = 4 then (Rw - 4) = 0. 0 *19 = 0 and 0 +1 = 1. Similarly Rw = 4 then (Rw - 3) = 1. 1 *19 = 1. 1 & ":" & 19 = 1:19. So .Rows(1:19).Hidden = blHide
If Rw = 5 then (Rw - 4) = 1. 1 *19 = 1 and 19 +1 = 20. Similarly Rw = 5 then (Rw - 3) = 2. 2 *19 = 38. 20 & ":" & 38 = 20:38. So .Rows(20:38).Hidden = blHide
etc.
Rows are never added to the workbook.

Awesome. I totally understand now. Thanks again for all your help.

Edit: I kind of thought it was a math equation. I was thinking why would you add + 1 to zero, but it really makes sense when you get to the next box in I5, and so on.

Since the outcomes for columns E & I are the same for all rows, and the only column-based difference concerns updating the "Daily Sales Tracking" worksheet if column I is being updated, your entire 1356 lines of code could be reduced to just 45 lines:

Please Login or Register  to view this content.

OK, so with 52 lines of code:

Please Login or Register  to view this content.