apsblackhorse
New Member
- Joined
- Jul 28, 2021
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi everyone, could someone help me refactor my code please. I am new on vba/macro coding. Hence, my coding skills doesn't that good. Wanted to learn to have an efficient macro/vba scripts. Attached were the codes. Thanks in advance.
VBA Code:
' Hide / Show Rows
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim xCellsA As String
Dim r As Long
If Not Intersect(Range("B13"), Target) Is Nothing Then
Cancel = True
r = 5 * Target.Row - 51
xCellsA = r & ":" & r + 6
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsB As String
Dim b As Long
If Not Intersect(Range("B21"), Target) Is Nothing Then
Cancel = True
b = 5 * Target.Row - 83
xCellsA = b & ":" & b + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsC As String
Dim c As Long
If Not Intersect(Range("B24"), Target) Is Nothing Then
Cancel = True
c = 5 * Target.Row - 95
xCellsA = c & ":" & c + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsD As String
Dim d As Long
If Not Intersect(Range("B27"), Target) Is Nothing Then
Cancel = True
d = 5 * Target.Row - 107
xCellsA = d & ":" & d + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsE As String
Dim e As Long
If Not Intersect(Range("b33"), Target) Is Nothing Then
Cancel = True
e = 5 * Target.Row - 131
xCellsA = e & ":" & e + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsF As String
Dim f As Long
If Not Intersect(Range("b39"), Target) Is Nothing Then
Cancel = True
f = 5 * Target.Row - 155
xCellsA = f & ":" & f + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsG As String
Dim g As Long
If Not Intersect(Range("b45"), Target) Is Nothing Then
Cancel = True
g = 5 * Target.Row - 179
xCellsA = g & ":" & g + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsH As String
Dim h As Long
If Not Intersect(Range("b51"), Target) Is Nothing Then
Cancel = True
h = 5 * Target.Row - 203
xCellsA = h & ":" & h + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsI As String
Dim i As Long
If Not Intersect(Range("b54"), Target) Is Nothing Then
Cancel = True
i = 5 * Target.Row - 215
xCellsA = i & ":" & i + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsJ As String
Dim j As Long
If Not Intersect(Range("b60"), Target) Is Nothing Then
Cancel = True
j = 5 * Target.Row - 239
xCellsA = j & ":" & j + 6
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsK As String
Dim k As Long
If Not Intersect(Range("b68"), Target) Is Nothing Then
Cancel = True
k = 5 * Target.Row - 271
xCellsA = k & ":" & k + 6
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsL As String
Dim l As Long
If Not Intersect(Range("b76"), Target) Is Nothing Then
Cancel = True
l = 5 * Target.Row - 303
xCellsA = l & ":" & l + 5
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsM As String
Dim m As Long
If Not Intersect(Range("b83"), Target) Is Nothing Then
Cancel = True
m = 5 * Target.Row - 331
xCellsA = m & ":" & m + 3
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsN As String
Dim n As Long
If Not Intersect(Range("b88"), Target) Is Nothing Then
Cancel = True
n = 5 * Target.Row - 351
xCellsA = n & ":" & n + 7
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsO As String
Dim o As Long
If Not Intersect(Range("b97"), Target) Is Nothing Then
Cancel = True
o = 5 * Target.Row - 387
xCellsA = o & ":" & o + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsP As String
Dim p As Long
If Not Intersect(Range("b101"), Target) Is Nothing Then
Cancel = True
p = 5 * Target.Row - 403
xCellsA = p & ":" & p + 11
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsQ As String
Dim q As Long
If Not Intersect(Range("b114"), Target) Is Nothing Then
Cancel = True
q = 5 * Target.Row - 455
xCellsA = q & ":" & q + 3
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsS As String
Dim s As Long
If Not Intersect(Range("b119"), Target) Is Nothing Then
Cancel = True
s = 5 * Target.Row - 475
xCellsA = s & ":" & s + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsU As String
Dim u As Long
If Not Intersect(Range("b123"), Target) Is Nothing Then
Cancel = True
u = 5 * Target.Row - 491
xCellsA = u & ":" & u + 6
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsV As String
Dim v As Long
If Not Intersect(Range("b131"), Target) Is Nothing Then
Cancel = True
v = 5 * Target.Row - 523
xCellsA = v & ":" & v + 0
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsW As String
Dim w As Long
If Not Intersect(Range("b133"), Target) Is Nothing Then
Cancel = True
w = 5 * Target.Row - 531
xCellsA = w & ":" & w + 12
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsX As String
Dim x As Long
If Not Intersect(Range("b147"), Target) Is Nothing Then
Cancel = True
x = 5 * Target.Row - 587
xCellsA = x & ":" & x + 11
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsY As String
Dim y As Long
If Not Intersect(Range("b160"), Target) Is Nothing Then
Cancel = True
y = 5 * Target.Row - 639
xCellsA = y & ":" & y + 5
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsZ As String
Dim z As Long
If Not Intersect(Range("b167"), Target) Is Nothing Then
Cancel = True
z = 5 * Target.Row - 667
xCellsA = z & ":" & z + 3
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAB As String
Dim ab As Long
If Not Intersect(Range("b172"), Target) Is Nothing Then
Cancel = True
ab = 5 * Target.Row - 687
xCellsA = ab & ":" & ab + 5
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAC As String
Dim ac As Long
If Not Intersect(Range("b179"), Target) Is Nothing Then
Cancel = True
ac = 5 * Target.Row - 715
xCellsA = ac & ":" & ac + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAD As String
Dim ad As Long
If Not Intersect(Range("b183"), Target) Is Nothing Then
Cancel = True
ad = 5 * Target.Row - 731
xCellsA = ad & ":" & ad + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAE As String
Dim ae As Long
If Not Intersect(Range("b187"), Target) Is Nothing Then
Cancel = True
ae = 5 * Target.Row - 747
xCellsA = ae & ":" & ae + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAF As String
Dim af As Long
If Not Intersect(Range("b190"), Target) Is Nothing Then
Cancel = True
af = 5 * Target.Row - 759
xCellsA = af & ":" & af + 7
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAG As String
Dim ag As Long
If Not Intersect(Range("b199"), Target) Is Nothing Then
Cancel = True
ag = 5 * Target.Row - 795
xCellsA = ag & ":" & ag + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAH As String
Dim ah As Long
If Not Intersect(Range("b202"), Target) Is Nothing Then
Cancel = True
ah = 5 * Target.Row - 807
xCellsA = ah & ":" & ah + 6
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAI As String
Dim ai As Long
If Not Intersect(Range("b213"), Target) Is Nothing Then
Cancel = True
ai = 5 * Target.Row - 851
xCellsA = ai & ":" & ai + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAJ As String
Dim aj As Long
If Not Intersect(Range("b216"), Target) Is Nothing Then
Cancel = True
aj = 5 * Target.Row - 863
xCellsA = aj & ":" & aj + 14
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAK As String
Dim ak As Long
If Not Intersect(Range("b232"), Target) Is Nothing Then
Cancel = True
ak = 5 * Target.Row - 927
xCellsA = ak & ":" & ak + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAL As String
Dim al As Long
If Not Intersect(Range("b236"), Target) Is Nothing Then
Cancel = True
al = 5 * Target.Row - 943
xCellsA = al & ":" & al + 7
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAM As String
Dim am As Long
If Not Intersect(Range("b245"), Target) Is Nothing Then
Cancel = True
am = 5 * Target.Row - 979
xCellsA = am & ":" & am + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAMM As String
Dim amm As Long
If Not Intersect(Range("b248"), Target) Is Nothing Then
Cancel = True
amm = 5 * Target.Row - 991
xCellsA = amm & ":" & amm + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAN As String
Dim an As Long
If Not Intersect(Range("b252"), Target) Is Nothing Then
Cancel = True
an = 5 * Target.Row - 1007
xCellsA = an & ":" & an + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAO As String
Dim ao As Long
If Not Intersect(Range("b256"), Target) Is Nothing Then
Cancel = True
ao = 5 * Target.Row - 1023
xCellsA = ao & ":" & ao + 2
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAP As String
Dim ap As Long
If Not Intersect(Range("b260"), Target) Is Nothing Then
Cancel = True
ap = 5 * Target.Row - 1039
xCellsA = ap & ":" & ap + 0
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAQ As String
Dim aq As Long
If Not Intersect(Range("b262"), Target) Is Nothing Then
Cancel = True
aq = 5 * Target.Row - 1047
xCellsA = aq & ":" & aq + 1
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAR As String
Dim ar As Long
If Not Intersect(Range("b265"), Target) Is Nothing Then
Cancel = True
ar = 5 * Target.Row - 1059
xCellsA = ar & ":" & ar + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAT As String
Dim at As Long
If Not Intersect(Range("b271"), Target) Is Nothing Then
Cancel = True
at = 5 * Target.Row - 1083
xCellsA = at & ":" & at + 0
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
Dim xCellsAU As String
Dim au As Long
If Not Intersect(Range("b273"), Target) Is Nothing Then
Cancel = True
au = 5 * Target.Row - 1091
xCellsA = au & ":" & au + 4
If Target.Value = "+" Then
Rows(xCellsA).Hidden = False
Target.Value = "-"
Else
Rows(xCellsA).Hidden = True
Target.Value = "+"
End If
End If
'Pre Job Process Tab - Status selection (Yes/No/NA)
If Not Intersect(Range("H14:H20,H22:H22,H25:H26,H28:H32,H34:H38,H40:H44,H46:H50,H52:H53,H55:H56"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
'Draft Job Process Tab - Status selection (Yes/No/NA)
If Not Intersect(Range("H61:H67,H69:H75,H77:H82,H84:H87,H89:H96,H98:H100,H102:H113,H115:H118,H20:H122,H124:H130,H132:H132,H134:H134,H136:H146,H148:H159,H161:H166,H168:H171,H173:H178,H180:H182,H184:H186,H188:H189,H191:H198,H200:H201,H203:H209"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
'Pre Reveiew Process Tab - Status selection (Yes/No/NA)
If Not Intersect(Range("H214:H215,H217:H217,H219:H219,H221:H231,H233:H235,H237:H244,H246:H247,H249:H251,H253:H255,H257:H259,H261:H261,H263:H264,H266:H270,H272:H272,H274:H278"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
'Review Process Tab - Status selection (Yes/No/NA)
If Not Intersect(Range("H282:H287"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
'OFS Process Tab - Status selection (Yes/No/NA)
If Not Intersect(Range("H290:H294"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 And Target.Row = 3 Then
If Target.Value = "Company" Then
Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = True
Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = False
ElseIf Target.Value = "Trust, Partnership & Sole Trader" Then
Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = False
Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
ElseIf Target.Value = "All" Then
Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263,65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
End If
End If
End Sub