Programatically deleting Range

[Grant Reid]

Hi

In a particular worksheet I have a number of named ranges. In cell "AM2" I
have entered the name of a particular named range. I'm trying to put
together a piece of code that will look at the named range value entered in
"AM2", select that particular range, deleting it and then shifting the cells
beneath it up. This is what I have at the moment, but it's not working for
me (I get a Run_time error '1004' Method 'Range' of object '_Global' failed
error message).

Sub test()
Dim strQueryName As String

strQueryName = ActiveSheet.Range("AM2")

Range(strQueryName).Select
Selection.Delete Shift:=xlUp
End Sub

Hope someone can help - Regards, Grant

 

[Nikos Yannacopoulos]

Grant,

Try changing line:

strQueryName = ActiveSheet.Range("AM2")

to:

strQueryName = ActiveSheet.Range("AM2").Value

HTH,
Nikos

 

[Jan Karel Pieterse]

Hi Grant,

Sub test()
Dim strQueryName As String

strQueryName = ActiveSheet.Range("AM2")

Range(strQueryName).Select
Selection.Delete Shift:=xlUp
End Sub

Alternatively:

Sub test()
Activesheet.names(ActiveSheet.Range("AM2").Value).ReferstoRange.Delete
Shift:=xlUp
End Sub

Note this sub contains a single line of code (word wrap might change
that in this message)

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Grant Reid]

Hi

I've actually determined that there was actually nothing wrong with my
original code.

The problem is that I also use the range name defined in "AM2" to
programatically create a named ODBC database query. Each time this routine
is executed it uses the range name defined in "AM2" to name the range.
However due the fact that this action appends an underscore and a number
after the range name, my code will not perform the desired action on the
range. For example, if the value in "AM2" is "DataRange" it will be named
"DataRange_1 (my code is looking for "DataRange").

So trick is to somehow get my code to reference "AM2" and check if there is
a named range in that worksheet that begins with value in "AM2". I'm at a
loss as to how to accomplish this and would be most gratefull if someone
could assist in putting the finishing touches to my original code.

Sub delrange()
Dim strQueryName As String
strQueryName = ActiveSheet.Range("AM2")
Range(strQueryName).Select
Selection.Delete Shift:=xlUp
End Sub

Many thanks to those who have responded thus far. Regards - Grant

 

[onedaywhen]

...

I also use the range name defined in "AM2" to
programatically create a named ODBC database query.

if the value in "AM2" is "DataRange" it will be named
"DataRange_1 (my code is looking for "DataRange").

What do you mean by 'a named ODBC database query'?

The reason I ask is you can clear a named range (i.e. delete the cell
contents, leave the definition in place, cells don't shifted up) using
SQL, provided it contains no cell formulas e.g.

DROP TABLE MyNamedRange

and then recreate it:

CREATE TABLE MyNamedRange
(
Col1 INTEGER,
Col2 INTEGER,
)

If the shape of the new table is different from the original named
range, the named range's definition will change to match. You can do
the same with worksheets:

DROP TABLE [Sheet1$]
;
CREATE TABLE [Sheet1$]
(
Col1 INTEGER,
Col2 INTEGER,
)
;

Again, the worksheet remains in the workbook, the data is merely
cleared, and when recreated the sheet is 'recycled'.

I recall an occasion where I did the above and a new sheet was created
called [Sheet1_1$] i.e. a similar result to your description:

DataRange will be named DataRange_1

However, I can't now recreate this scenario. I wondered if you were
doing something similar in your 'named ODBC database query' and could
share some details.

--

 

[onedaywhen]

Oops, typos! Should be:

CREATE TABLE MyNamedRange
(
Col1 INTEGER,
Col2 INTEGER
)
;

CREATE TABLE [Sheet1$]
(
Col1 INTEGER,
Col2 INTEGER
)
;

--

 

[Grant Reid]

Hi

Thanks for the response. OK, some more detail. On a number of worksheets I
have defined areas (not named ranges - just common location) which hold the
following data;
"AM2" contains Query Name (Server1_Detail - varies from worksheet to
worksheet)
"AM3" contains a connection string (ODBC;driver=SYBASE ASE ODBC
Driver;NA=158.76.47.9,5000;DB=master;UID=sa;PWD=) varies from worksheet to
worksheet
"AL5:AL27" contains a SQL query (Queries vary from worksheet to worksheet)

I have added a button on each of these worksheets and attached a macro. This
macro queries my database and populates the worksheet (see code below);

Sub Gen_SQL()
Dim strConn As String
Dim strSQL As Variant
Dim strQueryName As String

strConn = ActiveSheet.Range("AM3").Value
strSQL = ActiveSheet.Range("AL5:AL27").Value
strQueryName = ActiveSheet.Range("AM2").Value

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
'MsgBox "End of Routine Sub Gen_SQL"
'Call CalcColorScheme
'Call LinkDetail
End Sub

Now I also have another button on the page that is attached to macro. This
macro's purpose is to destroy the data and the range defined by the above
routine. As I explained, the problem is that each time the routine above is
executed it appends an underscore and a number to the name that I have
defined. To complicate matters, this number is incremented after every
execution.

Hope this provides some clarity and that someone out there can help.

Many Thanks - Grant

 

[Jan Karel Pieterse]

Hi Grant,

Now I also have another button on the page that is attached to macro. This
macro's purpose is to destroy the data and the range defined by the above
routine. As I explained, the problem is that each time the routine above is
executed it appends an underscore and a number to the name that I have
defined. To complicate matters, this number is incremented after every
execution.

I guess that you have to step through the QueryTables collection of each
sheet:

Dim oQt as QueryTable
Dim oSh as Worksheet
For each oSh in activeworkbook.Worksheets
For Each oQt in oSh.QueryTables
If Left(oQt.Name,8)="NameOfQt" Then
oQt.Delete 'untested
End If
Next
Next

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jan Karel Pieterse]

Hi Jan,

select the Standard bar (top one) and click reset.

I meant the Worksheet menubar of course!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Grant Reid]

Hi

Thanks to all who have responded. I've pretty much got the answer I needed
and scripted my code accordingly. There's only one problem....... After
running the code repeatedly, clicking on the Insert - Name - Define Menu
reveals defined names "Server1_Detail_1", "Server1_Detail_2",
"Server1_Detail_3" etc etc all with the similar invalid references ie
=Sheet2!#REF!.

Can anyone enlighten me how to remove these defined names and their invalid
references within the routine "DeleteAllQueries". The value in "D2"
determines the QueryTable name to be deleted and must also be used to delete
these invalid defined names.

Sub DeleteAllQueries()
Dim qt As QueryTable
Dim WSh As Worksheet
Dim strQueryName

For Each WSh In ThisWorkbook.Worksheets
strQueryName = WSh.Range("D2")
For Each qt In WSh.QueryTables
If InStr(qt.Name, strQueryName) Then
qt.ResultRange.ClearContents
qt.ResultRange.Delete
qt.Delete
End If
Next qt
Next WSh

End Sub

And for completeness, here is the code I use to generate the QueryTables;

Sub Gen_SQL()
Dim strConn As String
Dim strSQL As Variant
Dim strQueryName As String

strConn = ActiveSheet.Range("D3").Value
strSQL = ActiveSheet.Range("D5").Value
strQueryName = ActiveSheet.Range("D2").Value

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

Many Thanks - Grant

 

[Jan Karel Pieterse]

Hi Grant,

Thanks to all who have responded. I've pretty much got the answer I needed
and scripted my code accordingly. There's only one problem....... After
running the code repeatedly, clicking on the Insert - Name - Define Menu
reveals defined names "Server1_Detail_1", "Server1_Detail_2",
"Server1_Detail_3" etc etc all with the similar invalid references ie
=Sheet2!#REF!.

LIke this:

Sub DeleteAllQueries()
Dim qt As QueryTable
Dim WSh As Worksheet
Dim strQueryName
Dim oName as Name
For Each WSh In ThisWorkbook.Worksheets
strQueryName = WSh.Range("D2")
For Each qt In WSh.QueryTables
If InStr(qt.Name, strQueryName) Then
qt.ResultRange.ClearContents
qt.ResultRange.Delete
qt.Delete
End If
for each oName in Activeworkbook.Names
If Instr(oName.Name,strQueryname) Then
oName.Delete
End If
Next oName
Next qt
Next WSh

End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com