Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
Grant,
Try changing line: strQueryName = ActiveSheet.Range("AM2") to: strQueryName = ActiveSheet.Range("AM2").Value HTH, Nikos "Grant Reid" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
"Grant Reid" wrote ...
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
Oops, typos! Should be:
CREATE TABLE MyNamedRange ( Col1 INTEGER, Col2 INTEGER ) ; CREATE TABLE [Sheet1$] ( Col1 INTEGER, Col2 INTEGER ) ; -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically deleting Range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting or changing a range name | Excel Discussion (Misc queries) | |||
Deleting Range name's listed in the range address box. | Excel Discussion (Misc queries) | |||
Deleting range names | Excel Programming | |||
Rename a Range, programatically? | Excel Programming | |||
Deleting range not a row | Excel Programming |