Solved: loop through named area

[Rejje]

Hi! Still learning th basics I have got a problem I can't manage a solution by myself but probably is really a piece-of-cake for quite a few who'd read this.

Thing is below functional sub really loops through an area named "EXPORTLIST" and in which are the addresses to other named cells/areas in different worksheets that often need to be erased. However I have had to use regular cell "F37" and then loop from there.

What I would like to see is what a loop in "EXPORTLIST" would look like that keeps on as long as it's in that area.

Anyone?

Area "EXPORTLIST" below

EXP_1010 EXP_1020 EXP_1030 onto EXP_6040



Sub EraseAll()

Dim answer As Integer
Dim row As Integer
answer = MsgBox("Do you really want to erase all data?", vbYesNo)
If answer = 6 Then

Application.ScreenUpdating = False
Application.Calculation = xlManual
row = Worksheets("V").Range("F37").Row

While (Left(Worksheets("V").Range("F" & row).Value, 3) = "EXP")

Range(Worksheets("V").Range("F" & row).Value).Select
Selection.ClearContents
row = row + 1

Wend

Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End If

End Sub

[Bob Phillips]

For Each cell In Range("EXPORTLIST")

'do stuff
Next cell

[Rejje]

For Each cell In Range("EXPORTLIST")

'do stuff
Next cell


I tried this now but I still don't get it to loop. I know you'll immediately see what's wrong! I tell you I'm very basic...


Sub RaderaAllt2()
Dim answer As Integer
answer = MsgBox("Vill du verkligen radera allt?", vbYesNo)
If answer = 6 Then
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each cell In Range("EXPORTLIST")
Range(Range("EXPORTLIST").Value).Select
Selection.ClearContents
Next cell

Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End If

End Sub

[Tinbendr]

No need for a loop there.

If you're just deleting the contents of a named range.

Sub RaderaAllt2()
Dim answer As Integer
answer = MsgBox("Vill du verkligen radera allt?", vbYesNo)
If answer = 6 Then
Range("EXPORTLIST").ClearContents
End If
End Sub



which are the addresses to other named cells/areas in different worksheets that often need to be erased.
If you're doing some conditional delete in the range, then yes, a loop will be needed.

[Rejje]

No need for a loop there.

If you're just deleting the contents of a named range.

Sub RaderaAllt2()
Dim answer As Integer
answer = MsgBox("Vill du verkligen radera allt?", vbYesNo)
If answer = 6 Then
Range("EXPORTLIST").ClearContents
End If
End Sub



If you're doing some conditional delete in the range, then yes, a loop will be needed.

No, it's not the contents in "EXPORTLIST" I want to delete. In every single cell in this list is the reference to a range that should be deleted of contents.

[Bob Phillips]

Sub RaderaAllt2()
Dim answer As vbMsgBoxResult
answer = MsgBox("Vill du verkligen radera allt?", vbYesNo)
If answer = 6 Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Range("EXPORTLIST")
Range(cell.Value).ClearContents
Next cell

Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End If

End Sub

[Rejje]

I'm really greatful xld!

Code works perfect. However since a lot of the target areas to delete are merged I've had to modify some as below.

I also see the point in "Dim answer As VbMsgBoxResult" rather than integer. Once more thanks alot!

Sub RaderaAllt3()
Dim answer As VbMsgBoxResult
answer = MsgBox("Vill du verkligen radera allt?", vbYesNo)
If answer = 6 Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Range("EXPORTLISTA")
Range(cell.Value).Select
Selection.ClearContents
Next cell

Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("All data är raderad!")

End If

End Sub

[Bob Phillips]

I also should have changed



If answer = 6 Then


to



If answer = vbYes Then


it is more explanatory.

[Rejje]

I see and agree - logic should always be the guiding star!