Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
The below code works, but I need to be able to select the sheet first. Is it
possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Dim rng As Range
With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Bob,
Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Bob,
I just found a catch, if the cell contains a formula that resolves to a blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the cells containing a formula. I am trying to capture only the cells that have a value other than "", is this possible? -- Trefor "Trefor" wrote: Bob, Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Trefor,
Do you mean that it goes to the end of the column? If so, if say Data_EMCSoftware is blank, what should be selected/ -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... Bob, I just found a catch, if the cell contains a formula that resolves to a blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the cells containing a formula. I am trying to capture only the cells that have a value other than "", is this possible? -- Trefor "Trefor" wrote: Bob, Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
I'd just look.
Dim DestCell as Range .... With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") set DestCell = rng.end(xldown) do if destcell.row = rng.row then 'stop looking, at the top of the range exit do end if if destcell < "" then exit do else 'come up a row and continue testing set destcell = destcell.offset(-1,0) end if loop .Range(rng, destcell)).Name = "Data_EMCSoftwareList" ..... Trefor wrote: Bob, I just found a catch, if the cell contains a formula that resolves to a blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the cells containing a formula. I am trying to capture only the cells that have a value other than "", is this possible? -- Trefor "Trefor" wrote: Bob, Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Dave,
Sorry for the delay in getting back to you. This worked a treat thankyou. -- Trefor "Dave Peterson" wrote: I'd just look. Dim DestCell as Range .... With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") set DestCell = rng.end(xldown) do if destcell.row = rng.row then 'stop looking, at the top of the range exit do end if if destcell < "" then exit do else 'come up a row and continue testing set destcell = destcell.offset(-1,0) end if loop .Range(rng, destcell)).Name = "Data_EMCSoftwareList" ..... Trefor wrote: Bob, I just found a catch, if the cell contains a formula that resolves to a blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the cells containing a formula. I am trying to capture only the cells that have a value other than "", is this possible? -- Trefor "Trefor" wrote: Bob, Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with named ranges
Bob,
Thankyou for your reply, Dave nailed it in his reply. -- Trefor "Bob Phillips" wrote: Trefor, Do you mean that it goes to the end of the column? If so, if say Data_EMCSoftware is blank, what should be selected/ -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... Bob, I just found a catch, if the cell contains a formula that resolves to a blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the cells containing a formula. I am trying to capture only the cells that have a value other than "", is this possible? -- Trefor "Trefor" wrote: Bob, Perfect, many thanks. -- Trefor "Bob Phillips" wrote: Dim rng As Range With Workbooks(WorkbookMain).Worksheets("sheetname") Set rng = .Range("Data_EMCSoftware") .Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList" Set rng = .Range("Data_SoftwareExclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList" Set rng = .Range("Data_SoftwareInclude") .Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList" End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Trefor" wrote in message ... The below code works, but I need to be able to select the sheet first. Is it possible to do this with selecting the sheet, which in my case is (Visible = False) Sheets("sheetname").Select With Workbooks(WorkbookMain).Sheets("sheetname") .Range("Data_EMCSoftware").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_EMCSoftwareList" .Range("Data_SoftwareExclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareExludeList" .Range("Data_SoftwareInclude").Select .Range(Selection, Selection.End(xlDown)).Select Selection.name = "Data_SoftwareIncludeList" End With -- Trefor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges | Excel Worksheet Functions | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming | |||
Named Ranges | Excel Programming |