Locate cell name using variable and looping

Hi everybody

I have named some cells in a worksheet in the following way: Skjul1, Skjul2, Skjul3... Skjul69, Skjul70.

Now I would like to make a vba script that locates each cell and checks the value of the cell. If the value = 0, the entire row should be hidden.

I thought that I could make a loop like the following:

Private Sub Skjul()
Dim i As Long
i = 0
Do Until i = 75
If Sheets("spec").Range("Skjul" & i).Value = "0" Then
Sheets("spec").Range("Skjul" & i).EntireRow.Hidden = True
Else
Sheets("spec").Range("Skjul" & i).EntireRow.Hidden = False
End If
On Error GoTo 0
i = i + 1
Loop Until i = 75
End Sub

... but it doesn't work! I get the error: "Run-time error '1004': Application-defined or object-defined error".

Can anyone help me? I would really appreciate the effort!

Hi Thomas,

Your codepart "Skjul" & i might create the problem
Try this

Sub hideSkjul()
Dim i As Integer
Dim sName As String

For i = 1 To 75
sName = "Skjul" & Trim(CStr(i))
If Sheets("spec").Range(sName).Value = 0 Then
Sheets("spec").Range(sName).EntireRow.Hidden = True
Else
Sheets("spec").Range(sName).EntireRow.Hidden = False
End If
Next
End Sub

Hoop This Helps

Wouter Magre

Hi Thomas,

The following worked for me
'==============>>
Sub Skjul()
Dim i As Long
For i = 1 To 75
On Error Resume Next 'In case range does not exist
With Sheets("spec").Range("skjul" & i)
.EntireRow.Hidden = .Value = 0
End With
Next
On Error GoTo 0
End Sub
'<<==============

---
Regards,
Norman



"Thomas Olesen" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi everybody
>
> I have named some cells in a worksheet in the following way: Skjul1,
> Skjul2, Skjul3... Skjul69, Skjul70.
>
> Now I would like to make a vba script that locates each cell and checks
> the value of the cell. If the value = 0, the entire row should be
> hidden.
>
> I thought that I could make a loop like the following:
>
> Private Sub Skjul()
> Dim i As Long
> i = 0
> Do Until i = 75
> If Sheets("spec").Range("Skjul" & i).Value = "0" Then
> Sheets("spec").Range("Skjul" & i).EntireRow.Hidden =
> True
> Else
> Sheets("spec").Range("Skjul" & i).EntireRow.Hidden =
> False
> End If
> On Error GoTo 0
> i = i + 1
> Loop Until i = 75
> End Sub
>
> .. but it doesn't work! I get the error: "Run-time error '1004':
> Application-defined or object-defined error".
>
> Can anyone help me? I would really appreciate the effort!
>
>
> --
> Thomas Olesen
> ------------------------------------------------------------------------
> Thomas Olesen's Profile:
> http://www.excelforum.com/member.php...o&userid=28025
> View this thread: http://www.excelforum.com/showthread...hreadid=475313
>

Hi Norman & Wouter Magre

Thanks a lot for your help - It works like a charm!

I spent 5 hours already trying all sorts of combinations, since I am a beginner at this, so you have been very helpful.

Thanks again.

Thomas Olesen