If then .... else

G

Grant Reid

Hi

I need to check if a named range exists in the current worksheet and if so,
delete the range, otherwise continue with some other arbitary action.

Hope someone can help.

Many Thanks - Grant
 
K

kkknie

There is more than one way to do it, so here's my solution:

Code
-------------------
Sub test()

Dim n As Name
Dim strName As String
Dim bFound As Boolean

strName = "YourRangeName"
bFound = False

For Each n In ActiveWorkbook.Names
If n.Name = strName Then bFound = True
Next

If bFound = True Then
'If you want to remove the range name (but not the data) use
ActiveWorkbook.Names(strName).Delete

'If you want to delete the data use
Range(strName).Clear
'OR
Range(strName).Delete shift:=xlToLeft

Else

'Other code goes here

End If

End Su
 
C

chris

For Each Nm In ActiveWorkbook.Name
If Nm.Name = "MyRange" Then Nm.Delet
Nex
----- Grant Reid wrote: ----

H

I need to check if a named range exists in the current worksheet and if so
delete the range, otherwise continue with some other arbitary action

Hope someone can help

Many Thanks - Gran
 
G

Grant Reid

Thanks for the reply. My VBA skills are virtually null, so your help was
much appreciated.

However, I have stumbled accross another problem but I hope that a slight
tweak of your code will do the trick. I have a number of named ranges on the
page that I need to perform the required actions upon. The naming
conventions will probably make things somewhat easier.

Assume the ranges are as follows;
Range_1
Range_2
Range_3 and
Range_4

I need to perform these actions on all the above ranges. How will the code
you provided be modified to accomodate this?

Many Thanks - Grant
 
K

kkknie

Change to:

Code
-------------------
Sub test()

Dim n As Name
Dim strName As String
Dim bFound As Boolean
Dim i as Integer '<--NEW

For i = 1 to 4 '<--NEW

strName = "Range_" & i
bFound = False

For Each n In ActiveWorkbook.Names
If n.Name = strName Then bFound = True
Next

If bFound = True Then
'If you want to remove the range name (but not the data) use
ActiveWorkbook.Names(strName).Delete

'If you want to delete the data use
Range(strName).Clear
'OR
Range(strName).Delete shift:=xlToLeft

Else

'Other code goes here

End If

Next '<--NEW

End Su
 
G

Grant Reid

Hi

As you have probably gathered, I'm pretty clueless with VBA and am still
stuck. I have two pieces of code that do what I require, its just putting it
all together that has me stumped.

This piece of code queries a database and names the range based on the
contents of cell M2.

Sub Gen_SQL()
Dim strConn As String
Dim strSQL As Variant
Dim strQueryName As String
strConn = ActiveSheet.Range("M3")
strSQL = ActiveSheet.Range("L5:L23")
strQueryName = ActiveSheet.Range("M2")
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("A6"), Sql:=strSQL)
.Name = strQueryName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With
End Sub

The next piece of code deletes the range defined in M2

Sub delrange()
Dim strQueryName As String
strQueryName = ActiveSheet.Range("M2")
Application.Goto Reference:=strQueryName
Range(strQueryName).Clear
Selection.Delete shift:=xlUp
End Sub

What I'm trying to accomplish is the following;
[1] Test if the range exists.
[2] If it does exist it must delete the range calling Sub delrange and then
recreate the data range, calling Sub Gen_SQL
[3] If it does not exist it must just create the range calling Sub Gen_SQL

[4] I'm probably pushing my luck here, but here goes... In the Sub Gen_SQL,
I would like to declare the destination for the range as a variable, instead
of hard coding as A6. Can this be done?

Hope you can help - Many thanks, Grant
 
K

kkknie

Here's what I came up with:

Code
-------------------
Sub Gen_SQL()

Dim strConn As String
Dim strSQL As Variant
Dim strQueryName As String
Dim strRange As String '<---NEW

'Changed all Ranges to add .Value property (not required now but is in .NET)
strConn = ActiveSheet.Range("M3").Value
strSQL = ActiveSheet.Range("L5:L23").Value
strQueryName = ActiveSheet.Range("M2").Value
strRange = ActiveSheet.Range("M4").Value '<---NEW

Call DelRange(strQueryName) '<---NEW: Call this to delete your range if it exists

With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range(strRange), Sql:=strSQL)
.Name = strQueryName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With

End Sub

Sub DelRange(xstrRange)

Dim n As Name
Dim bFound As Boolean

bFound = False
For Each n In ActiveWorkbook.Names
If n.Name = xstrRange Then bFound = True
Next
If bFound = True Then
Range(xstrRange).Clear
ActiveWorkbook.Names(xstrRange).Delete
End If

End Su
-------------------

Basically, set up all of the information for your query, call th
DelRange routine to delete the range if it exists, then run the query.

Let me know what I'm missing. I do all my data imports with ADO rathe
than QueryTables, so I'm not too hip as to how they work. I'm assumin
that the .Name names the range rather than the querytable. If that i
not the case, we may have to make some modifications to DelRange.
Maybe:

Code
-------------------
Sub DelRange(xstrRange)

Dim n
Dim bFound As Boolean

bFound = False
For Each n In ActiveSheet.QueryTables
If n.Name = xstrRange Then bFound = True
Next
If bFound = True Then
ActiveSheet.QueryTables(xstrRange).Delete
End If

End Su
 
Top