Add formula for only visible cells

[andrewb90]

Hello all,

I have this code that I am using to increase cell values based on another cells contents, however I would like not change my cell values if the column in Data1 is hidden.

Sub add()Sheets("Score").Select
Range("D3").Value = Range("D3") + Sheets("Data1").Range("C3")
Range("E3").Value = Range("E3") + Sheets("Data1").Range("D3")
Range("F3").Value = Range("F3") + Sheets("Data1").Range("E3")
Range("G3").Value = Range("G3") + Sheets("Data1").Range("F3")
Range("H3").Value = Range("H3") + (Sheets("Data1").Range("I3") * Sheets("Data1").Range("H3"))
Range("I3").Value = Range("I3") + (Sheets("Data1").Range("J3") * Sheets("Data1").Range("H3"))
End Sub

 

[Michael M]

maybe...

Sub MM1()
With Sheets("Score")
    If Sheets("Data1").Columns("D").EntireColumn.Hidden = True Then Exit Sub
        .Range("D3").Value = Range("D3") + Sheets("Data1").Range("C3")
        .Range("E3").Value = Range("E3") + Sheets("Data1").Range("D3")
        .Range("F3").Value = Range("F3") + Sheets("Data1").Range("E3")
        .Range("G3").Value = Range("G3") + Sheets("Data1").Range("F3")
        .Range("H3").Value = Range("H3") + (Sheets("Data1").Range("I3") * Sheets("Data1").Range("H3"))
        .Range("I3").Value = Range("I3") + (Sheets("Data1").Range("J3") * Sheets("Data1").Range("H3"))
End With
End Sub

 

[andrewb90]

That cancels my entire sub if one column is hidden, I need to just have that line skipped if the column is hidden.
Using your code, I revised it to this, but it is lengthy already and as I add the remainder of my rows, it'l be really long. Any thoughts on how to shorten this?

Sub revised()
With Sheets("Score")
    If Sheets("Data1").Columns("C").EntireColumn.Hidden = False Then
        .Range("D3").Value = Range("D3") + Sheets("Data1").Range("C3")
    End If
    If Sheets("Data1").Columns("D").EntireColumn.Hidden = False Then
        .Range("E3").Value = Range("E3") + Sheets("Data1").Range("D3")
    End If
    If Sheets("Data1").Columns("E").EntireColumn.Hidden = False Then
        .Range("F3").Value = Range("F3") + Sheets("Data1").Range("E3")
    End If
    If Sheets("Data1").Columns("F").EntireColumn.Hidden = False Then
        .Range("G3").Value = Range("G3") + Sheets("Data1").Range("F3")
    End If
    If Sheets("Data1").Columns("I").EntireColumn.Hidden = False Then
        .Range("H3").Value = Range("H3") + (Sheets("Data1").Range("I3") * Sheets("Data1").Range("H3"))
    End If
    If Sheets("Data1").Columns("J").EntireColumn.Hidden = False Then
        .Range("I3").Value = Range("I3") + (Sheets("Data1").Range("J3") * Sheets("Data1").Range("H3"))
    End If
End With
End Sub

 

[Peter_SSs]

Try this for that part of the code

With Sheets("Data1")
  If Not .Columns("C").Hidden Then Range("D3").Value = Range("D3") + .Range("C3")
  If Not .Columns("D").Hidden Then Range("E3").Value = Range("E3") + .Range("D3")
  If Not .Columns("E").Hidden Then Range("F3").Value = Range("F3") + .Range("E3")
  If Not .Columns("F").Hidden Then Range("G3").Value = Range("G3") + .Range("F3")
  If Not .Columns("I").Hidden And Not .Columns("H").Hidden Then Range("H3").Value = Range("H3") + (.Range("I3") * .Range("H3"))
  If Not .Columns("I").Hidden And Not .Columns("J").Hidden Then Range("I3").Value = Range("I3") + (.Range("J3") * .Range("H3"))
End With