Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
Hi All........
I'm back with another Range problem, if someone would be so kind. I have a database of 12 columns wide and several hundred rows high. Column A has only values in it's cells corresponding with the beginning of a "datablock"...ie:, with a value in A1 and A10....all the data in row 1 to whatever row is just above the next value in column A.........(in this case row 9) is associated with cell A1 and I would like to create a range of that block of cells, starting with A1 as upper left cell and these rows and 12 columns wide (A1:L9 in this case) . The macro should continue to create the next block of data starting with the second value in column A and however many rows are below it to the next value in column A, etc etc...as example, the third value in column A might be in A14, so the next Range should be established as A10:L13. In actual practice, I will never know what the spacing down column A will be, nor how many values will be in column A, but the data will always be 12 columns wide. Each Range should bear the name of the value of it's respective Upper-left cell found in column A. Any assistance will be much appreciated..... TIA Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
No code written to handle a single row name (two contiguous cells in column
A containing data) Sub AddNames() Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim cell As Range Set rng = Cells(Rows.Count, 2).End(xlUp)(2, 0) rng.Value = "Dummy" Set rng1 = Columns(1).SpecialCells(xlConstants) For Each cell In rng1.Areas If cell.Count 1 Then ' no stated requirement to handle this Else If cell.Row < 1 Then Set rng2 = cell.Offset(-1, 0).End(xlUp) Range(rng2, cell.Offset(-1, 0)).Resize(, 12).Name = rng2.Text End If End If Next rng.EntireRow.Delete ActiveSheet.UsedRange End Sub -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........ I'm back with another Range problem, if someone would be so kind. I have a database of 12 columns wide and several hundred rows high. Column A has only values in it's cells corresponding with the beginning of a "datablock"...ie:, with a value in A1 and A10....all the data in row 1 to whatever row is just above the next value in column A.........(in this case row 9) is associated with cell A1 and I would like to create a range of that block of cells, starting with A1 as upper left cell and these rows and 12 columns wide (A1:L9 in this case) . The macro should continue to create the next block of data starting with the second value in column A and however many rows are below it to the next value in column A, etc etc...as example, the third value in column A might be in A14, so the next Range should be established as A10:L13. In actual practice, I will never know what the spacing down column A will be, nor how many values will be in column A, but the data will always be 12 columns wide. Each Range should bear the name of the value of it's respective Upper-left cell found in column A. Any assistance will be much appreciated..... TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
That code of yours is just absolutely SUPER, Tom.
It does exactly what I had hoped. Thank you so very very kindly! Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: No code written to handle a single row name (two contiguous cells in column A containing data) Sub AddNames() Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim cell As Range Set rng = Cells(Rows.Count, 2).End(xlUp)(2, 0) rng.Value = "Dummy" Set rng1 = Columns(1).SpecialCells(xlConstants) For Each cell In rng1.Areas If cell.Count 1 Then ' no stated requirement to handle this Else If cell.Row < 1 Then Set rng2 = cell.Offset(-1, 0).End(xlUp) Range(rng2, cell.Offset(-1, 0)).Resize(, 12).Name = rng2.Text End If End If Next rng.EntireRow.Delete ActiveSheet.UsedRange End Sub -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........ I'm back with another Range problem, if someone would be so kind. I have a database of 12 columns wide and several hundred rows high. Column A has only values in it's cells corresponding with the beginning of a "datablock"...ie:, with a value in A1 and A10....all the data in row 1 to whatever row is just above the next value in column A.........(in this case row 9) is associated with cell A1 and I would like to create a range of that block of cells, starting with A1 as upper left cell and these rows and 12 columns wide (A1:L9 in this case) . The macro should continue to create the next block of data starting with the second value in column A and however many rows are below it to the next value in column A, etc etc...as example, the third value in column A might be in A14, so the next Range should be established as A10:L13. In actual practice, I will never know what the spacing down column A will be, nor how many values will be in column A, but the data will always be 12 columns wide. Each Range should bear the name of the value of it's respective Upper-left cell found in column A. Any assistance will be much appreciated..... TIA Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
If I understand you correctly you want to capture blocks of cells adjacent to
a heading in column "A", for the next 11 columns. Here's what I came up with. It requires a temporary value in the row following the last row of data, and it's removed after the loop. Sub NameMyRanges() Dim CurrentBlock As Range Dim NextBlock As Range, EndBlock As Range Dim RangeToName As Range Set EndBlock = Cells(Rows.Count, 2).End(xlUp)(2, 0) EndBlock.Value = "End" Set CurrentBlock = Range("$A$1") Do Set NextBlock = CurrentBlock.End(xlDown) Set RangeToName = Range(CurrentBlock.Address, NextBlock.Offset(-1, 11)) RangeToName.Name = CurrentBlock.Value Set CurrentBlock = NextBlock Loop Until NextBlock = EndBlock EndBlock.ClearContents End Sub Regards, GS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
To assign a local name (sheet level), replace this line:
RangeToName.Name = CurrentBlock.Value with this one: RangeToName.Name = "'" & ActiveSheet.Name & "'!" & CurrentBlock.Value Regards, GS |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
You can also make a local name (sheet level) with
Activesheet.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) for Workbook Level ActiveWorkbook.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) -- Regards, Tom Ogilvy "GS" wrote in message ... To assign a local name (sheet level), replace this line: RangeToName.Name = CurrentBlock.Value with this one: RangeToName.Name = "'" & ActiveSheet.Name & "'!" & CurrentBlock.Value Regards, GS |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
Hi Tom,
Prior to my original post, there were no others. There seems to be a time delay for updating the forum. (I think we've seen this before) Had I seen your post, I wouldn't have bothered as it's good "as is".<FWIW The switch to using local names was an afterthought in case there were other sheets in the wbk that could possibly end up replacing global name references. (If I may...) -question regarding your interesting alternative to making the local names; why the (-1, -1, xlA1, True) when .Address is all that's necessary? <-Just curious!<g Regards, Garry "Tom Ogilvy" wrote: You can also make a local name (sheet level) with Activesheet.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) for Workbook Level ActiveWorkbook.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) -- Regards, Tom Ogilvy "GS" wrote in message ... To assign a local name (sheet level), replace this line: RangeToName.Name = CurrentBlock.Value with this one: RangeToName.Name = "'" & ActiveSheet.Name & "'!" & CurrentBlock.Value Regards, GS |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeName blocks of data
I guess because I didn't feel that address is all that is necessary.
-- Regards, Tom Ogilvy "GS" wrote in message ... Hi Tom, Prior to my original post, there were no others. There seems to be a time delay for updating the forum. (I think we've seen this before) Had I seen your post, I wouldn't have bothered as it's good "as is".<FWIW The switch to using local names was an afterthought in case there were other sheets in the wbk that could possibly end up replacing global name references. (If I may...) -question regarding your interesting alternative to making the local names; why the (-1, -1, xlA1, True) when .Address is all that's necessary? <-Just curious!<g Regards, Garry "Tom Ogilvy" wrote: You can also make a local name (sheet level) with Activesheet.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) for Workbook Level ActiveWorkbook.Names.Add Name:=CurrentBlock.Value, _ Refersto:="=" & RangeToName.Address(-1,-1,xlA1,True) -- Regards, Tom Ogilvy "GS" wrote in message ... To assign a local name (sheet level), replace this line: RangeToName.Name = CurrentBlock.Value with this one: RangeToName.Name = "'" & ActiveSheet.Name & "'!" & CurrentBlock.Value Regards, GS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort blocks of data | Excel Discussion (Misc queries) | |||
Count Blocks of Data | Excel Worksheet Functions | |||
blocks of data repeating | New Users to Excel | |||
averaging particular blocks of data | Excel Discussion (Misc queries) | |||
Copy Blocks Of Data | Excel Discussion (Misc queries) |