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!


--
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

Locate cell name using variable and looping
 

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

Locate cell name using variable and looping
 

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"
wrote in message
news:Thomas.Olesen.1ws4ui_1129104312.709@excelforu m-nospam.com...

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

Locate cell name using variable and looping
 

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


--
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