| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
I'm only interested in one row at a time. If I have values in B1, C1, D1
and nothing in E1, but continue with values in F1, etc. I want the macro to create a NamedRange of B1  1, regardless of the content of any other row.The values in my cells are all numbers presently, but hopefully the macro would also work with text values....no formulas now, but as you say "if they return the empty string (""), then you consider that to be an "empty cell".........that would be icing on the cake. Tks for the interest Vaya con Dios, Chuck, CABGx3 "Extra points"...... if the macro would also return the Column number/letter of the rightmost cell in the newly NamedRange. "Rick Rothstein" <(E-Mail Removed)> wrote in message news:uqc%(E-Mail Removed)... > Do you mean the first empty cell in a column to the right of B1 no matter > what row that cell is in (as long as it is not below the last piece of > data in Column B)? > > Also, what is in your cells... data or formulas? If formulas, I guess if > they return the empty string (""), then you consider that to be an "empty > cell", right? > > -- > Rick (MVP - Excel) > > > "clr" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> Well.........further testing shows that this code skips over empty cells >> and includes all cells out to the last filled in the row. That's not >> exactly the way I wanted it. I just wanted the contiguious ones from B1 >> rightward to the first empty one. >> >> Tks anyway, >> >> Vaya con Dios, >> Chuck, CABGx3 >> >> >> >> >> "JLGWhiz" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> This might be more than you need, but it assigns the name and then tests >>> to make sure that it took. >>> >>> Sub dk() >>> Dim lc As Long, rng As Range >>> lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column >>> Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc)) >>> rng.Name = "myRange" >>> Range("myRange").Interior.ColorIndex = 3 >>> End Sub >>> >>> >>> >>> >>> "CLR" <(E-Mail Removed)> wrote in message >>> news:C8686B66-323D-4FDC-9B1A-(E-Mail Removed)... >>>> Hi All....... >>>> I need help please to create a Named Range of all the cells with values >>>> therein starting at cell B1 and continuing through all contigunious >>>> cells >>>> with values to the right in the same row only. If there be a broken >>>> column, >>>> then all cells to the right of that break need not be included. >>>> >>>> Tks, >>>> Vaya con Dios, >>>> Chuck, CABGx3 >>>> >>>> >>> >>> >> >> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
If you only want the first group of filled cells on a row then:
Sub sl() Dim sh As Worksheet Set sh = ActiveSheet sh.Range("B1", Range("B1").End(xlToRight)).Name = "myName" MsgBox Range("myName").Address End Sub "clr" <(E-Mail Removed)> wrote in message news:OiQ$(E-Mail Removed)... > I'm only interested in one row at a time. If I have values in B1, C1, D1 > and nothing in E1, but continue with values in F1, etc. I want the macro > to create a NamedRange of B1 1, regardless of the content of any other> row. The values in my cells are all numbers presently, but hopefully the > macro would also work with text values....no formulas now, but as you say > "if they return the empty string (""), then you consider that to be an > "empty cell".........that would be icing on the cake. > > Tks for the interest > Vaya con Dios, > Chuck, CABGx3 > > "Extra points"...... if the macro would also return the Column > number/letter of the rightmost cell in the newly NamedRange. > > > > > > > > > "Rick Rothstein" <(E-Mail Removed)> wrote in message > news:uqc%(E-Mail Removed)... >> Do you mean the first empty cell in a column to the right of B1 no matter >> what row that cell is in (as long as it is not below the last piece of >> data in Column B)? >> >> Also, what is in your cells... data or formulas? If formulas, I guess if >> they return the empty string (""), then you consider that to be an "empty >> cell", right? >> >> -- >> Rick (MVP - Excel) >> >> >> "clr" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> Well.........further testing shows that this code skips over empty cells >>> and includes all cells out to the last filled in the row. That's not >>> exactly the way I wanted it. I just wanted the contiguious ones from B1 >>> rightward to the first empty one. >>> >>> Tks anyway, >>> >>> Vaya con Dios, >>> Chuck, CABGx3 >>> >>> >>> >>> >>> "JLGWhiz" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>>> This might be more than you need, but it assigns the name and then >>>> tests to make sure that it took. >>>> >>>> Sub dk() >>>> Dim lc As Long, rng As Range >>>> lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column >>>> Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc)) >>>> rng.Name = "myRange" >>>> Range("myRange").Interior.ColorIndex = 3 >>>> End Sub >>>> >>>> >>>> >>>> >>>> "CLR" <(E-Mail Removed)> wrote in message >>>> news:C8686B66-323D-4FDC-9B1A-(E-Mail Removed)... >>>>> Hi All....... >>>>> I need help please to create a Named Range of all the cells with >>>>> values >>>>> therein starting at cell B1 and continuing through all contigunious >>>>> cells >>>>> with values to the right in the same row only. If there be a broken >>>>> column, >>>>> then all cells to the right of that break need not be included. >>>>> >>>>> Tks, >>>>> Vaya con Dios, >>>>> Chuck, CABGx3 >>>>> >>>>> >>>> >>>> >>> >>> >> > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Now, that does it, with the greatest of cool..........thank you kind Sir!
Vaya con Dios, Chuck, CABGx3 "JLGWhiz" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)... > If you only want the first group of filled cells on a row then: > > Sub sl() > Dim sh As Worksheet > Set sh = ActiveSheet > sh.Range("B1", Range("B1").End(xlToRight)).Name = "myName" > MsgBox Range("myName").Address > End Sub > > > > "clr" <(E-Mail Removed)> wrote in message > news:OiQ$(E-Mail Removed)... >> I'm only interested in one row at a time. If I have values in B1, C1, >> D1 and nothing in E1, but continue with values in F1, etc. I want the >> macro to create a NamedRange of B1 1, regardless of the content of any>> other row. The values in my cells are all numbers presently, but >> hopefully the macro would also work with text values....no formulas now, >> but as you say "if they return the empty string (""), then you consider >> that to be an "empty cell".........that would be icing on the cake. >> >> Tks for the interest >> Vaya con Dios, >> Chuck, CABGx3 >> >> "Extra points"...... if the macro would also return the Column >> number/letter of the rightmost cell in the newly NamedRange. >> >> >> >> >> >> >> >> >> "Rick Rothstein" <(E-Mail Removed)> wrote in message >> news:uqc%(E-Mail Removed)... >>> Do you mean the first empty cell in a column to the right of B1 no >>> matter what row that cell is in (as long as it is not below the last >>> piece of data in Column B)? >>> >>> Also, what is in your cells... data or formulas? If formulas, I guess if >>> they return the empty string (""), then you consider that to be an >>> "empty cell", right? >>> >>> -- >>> Rick (MVP - Excel) >>> >>> >>> "clr" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>>> Well.........further testing shows that this code skips over empty >>>> cells and includes all cells out to the last filled in the row. That's >>>> not exactly the way I wanted it. I just wanted the contiguious ones >>>> from B1 rightward to the first empty one. >>>> >>>> Tks anyway, >>>> >>>> Vaya con Dios, >>>> Chuck, CABGx3 >>>> >>>> >>>> >>>> >>>> "JLGWhiz" <(E-Mail Removed)> wrote in message >>>> news:(E-Mail Removed)... >>>>> This might be more than you need, but it assigns the name and then >>>>> tests to make sure that it took. >>>>> >>>>> Sub dk() >>>>> Dim lc As Long, rng As Range >>>>> lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column >>>>> Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc)) >>>>> rng.Name = "myRange" >>>>> Range("myRange").Interior.ColorIndex = 3 >>>>> End Sub >>>>> >>>>> >>>>> >>>>> >>>>> "CLR" <(E-Mail Removed)> wrote in message >>>>> news:C8686B66-323D-4FDC-9B1A-(E-Mail Removed)... >>>>>> Hi All....... >>>>>> I need help please to create a Named Range of all the cells with >>>>>> values >>>>>> therein starting at cell B1 and continuing through all contigunious >>>>>> cells >>>>>> with values to the right in the same row only. If there be a broken >>>>>> column, >>>>>> then all cells to the right of that break need not be included. >>>>>> >>>>>> Tks, >>>>>> Vaya con Dios, >>>>>> Chuck, CABGx3 >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >> >> > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| clearing values of cells in named range(s) so the cells are empty | BRC | Microsoft Excel Programming | 1 | 10th Jan 2010 06:54 AM |
| Search used cells for values containing named range | mp | Microsoft Excel Programming | 2 | 25th Sep 2009 03:41 PM |
| Create named ranges WITHOUT using the Names Cells or Range | Fernando Fernandes | Microsoft Excel Programming | 16 | 28th Apr 2009 07:14 AM |
| Macro to select only cells containing values within named range | Code Numpty | Microsoft Excel Programming | 1 | 25th Nov 2008 07:36 AM |
| how do i create a named range excluding particular cells | =?Utf-8?B?Um9iRzIwMDc=?= | Microsoft Excel Misc | 4 | 25th Jul 2007 04:22 PM |
