Referencing a dynamic column

How do I reference a column that changes number of rows day to day?
I am running a macro that is filtering on a number of criteria, and I
want the macro to select the first value, replace that value with a new
value, and replace all the other values returned by the filter (the VBA
equivalent of Filter, Copy, SHIFT-CTRL-Down arrow key and Paste).  
I am sure that it is something fairly simple, but I've been trying for
four or five hours (doing other stuff at the same time), and I'm
stumped.  I've tried using Relative Reference in Macro Recorder, but
for some reason, it does not seem to want to cooperate with me.
Any suggestions would be greatly appreciated.
Thank you for your time,
Chris



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/22/2003 2:20:10 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
434 Views

Similar Articles

[PageSpeed] 51

Chris,

I'm not sure what you're asking.  If you want to determine if a particular
cell in a column has been hidden by the autofilter, examine the hidden
property of its entire row:

For Each thing In Range("MyRange")
If thing.EntireRow.Hidden = False Then thing.value= ...

If you want to know how far down the the values go, use

  Range("B2").End(xlDown)

This returns the last cell with something in it.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"ChrisBat" <ChrisBat.vpfro@excelforum-nospam.com> wrote in message
news:ChrisBat.vpfro@excelforum-nospam.com...
> How do I reference a column that changes number of rows day to day?
> I am running a macro that is filtering on a number of criteria, and I
> want the macro to select the first value, replace that value with a new
> value, and replace all the other values returned by the filter (the VBA
> equivalent of Filter, Copy, SHIFT-CTRL-Down arrow key and Paste).
> I am sure that it is something fairly simple, but I've been trying for
> four or five hours (doing other stuff at the same time), and I'm
> stumped.  I've tried using Relative Reference in Macro Recorder, but
> for some reason, it does not seem to want to cooperate with me.
> Any suggestions would be greatly appreciated.
> Thank you for your time,
> Chris
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
nowhere8060 (363)
10/22/2003 2:48:07 PM
Hi Chris,

Something like this maybe.  Seems I got this from Wilson.

Sub TheRange()
Dim lRow As Long
Dim dRange As Range
lRow = Cells(Rows.Count, "A").End(xlUp).Row
Set dRange = Range("A1:A" & lRow)
'dRange.Select
End Sub

HTH
Regards,
Howard

"ChrisBat" <ChrisBat.vpfro@excelforum-nospam.com> wrote in message
news:ChrisBat.vpfro@excelforum-nospam.com...
> How do I reference a column that changes number of rows day to day?
> I am running a macro that is filtering on a number of criteria, and I
> want the macro to select the first value, replace that value with a new
> value, and replace all the other values returned by the filter (the VBA
> equivalent of Filter, Copy, SHIFT-CTRL-Down arrow key and Paste).
> I am sure that it is something fairly simple, but I've been trying for
> four or five hours (doing other stuff at the same time), and I'm
> stumped.  I've tried using Relative Reference in Macro Recorder, but
> for some reason, it does not seem to want to cooperate with me.
> Any suggestions would be greatly appreciated.
> Thank you for your time,
> Chris
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
lhkittle1 (70)
10/22/2003 3:31:09 PM
Reply:

Similar Artilces:

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

Show dynamic content in dialog box
I want to achieve the same effect as a tab control but I don't want the tabs at the top to be visible... so the shown form is switched programmatically. Any advice or pointers are welcome ! Thanks Mike You would pretty much do the same thing as you would with tab. Place all the controls for each group inside a seperate child dialog. Then simply show and hide the different dialogs in the main dialog. AliR. "Mike Gleason Jr Couturier" <mikejrMAPSON@videotron.ca> wrote in message news:enxUBEH5EHA.1120@TK2MSFTNGP11.phx.gbl... > I want to achieve the same effect as &...

how to name many columns
how do you name a column to extend over several columns in excel? just select the columns and put a name in the NAME box next to the formula box -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "ben" <ben@discussions.microsoft.com> wrote in message news:4D949A09-DB62-426C-BEF3-04C12A7C5FA3@microsoft.com... > how do you name a column to extend over several columns in excel? Hi Ben I read your question slightly differently to Don, who gave you a method of creating a named range. I interpreted you request as wanting to e...

Data from one column set up into ten columns
I have some data in one column: 1. ABC 2. 3. xyz 4. 5. 123 6. aaa 7. 8. 9. 10. 11. DEF 12. 13. rst 14. 15. 456 16. bbb 17. 18. 19. 20. 21. GHI .... and so on... I'd like to set this data in one row but in ten columns, like this: ---A--- ---B--- ---C--- ---D--- ---E--- ---F--- ---G--- ---H--- ... 1. ABC xyz 123 aaa .... How it can be done? -- Tobi tobbi@poczta.onet.pl www.aukcje24.pl - najlepsze aukcje internetowe! Hi Tobi copy it, click in A2, choose edit / paste special -values then delete row 1 Cheers JulieD "Tobi" <tobbi@poczta....

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

Excel
I need to create an expense sheet in excel that has the same 'title' (e.g., "transportation", "business lunch", "advertising" read down the left side/columns, and "date", "cost", "vendor" read across the top/rows) information in column A (for example) on each page, for what will be a multi-page document. It seems that this would be resolved in a header-footer capacity, but I am uncertain of the proper procedure, partly because the info is rather complex. Help? See the "File->PageSetup->Sheet" tab. --...

Dynamics RMS Online Catalog
Hello Everyone, there is a new online Microsoft Dynamics community addon catalog available for everyone to access: http://www.microsoftdynamicsaddons.com/ It is searchable, and it has the ability to rank and leave comments on specific addons. Everyone is encouraged to look at the site and give their input so others can have more information about the solutions. Also ISV's are encouraged to list their addons on the site (it's free!) **This post was made per the recomendation of the Microsoft Community team: http://www.microsoft.com/dynamics/community/relatedcommunitieslandi...

sort 2 column in the same time "" as dictionary ""
Hello all I need to make sort of pages to be like a dictionary is that possible I put a test file on the next link http://rapidshare.com/files/382288320/index_test.xls.html I need to sort the 2 yellow column ,to be alphapetic page by page appreciate for help me Thanks Mahmoud ...

Reversed column labels
For some reason, when I open any new spreadsheets, the column labels are reversed, with column A on the right side of the screen. This is making working with excel difficult. Is there some option I enabled that can be disabled so that Excel gets back to normal? Thanks Andrew Remsen Hi Drew tools / options / international - check the settings under default direction note, you won't see the affect of this until you close & reopen your workbook. Regards julieD "Drew Remsen" <remsen59@yahoo.com> wrote in message news:MeSpd.73932$8G4.40962@tornado.tampabay.rr.com....

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

Column heading differences
The menu column headings on my spreadsheets today are numbers instead of letters! I want the letters back! I like the differentiation between rows (numbers) and columns (letters). Help?? I received and opened a virus-free spreadsheet from a co-worker. Her spreadsheet had numbers for the columns -- could that have made my default switch? How do I switch it back? <Tools> <Options> <General> tab, And *UNCHECK* "R1C1 Reference Style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benef...

how can I freeze column width (cell size)???
I have a spreadsheet with formulas. I show the formulas and resize (autofit) the columns. I want to freeze the column widths on all the columns then... .... uncheck show formulas. Problem: when I uncheck show formulas the columns resize (smaller) automatically. Many of the columns re-size to small for the data (pound signs display). Can someone tell me what I need to do to keep the columns from resizing after unchecking show formulas? Thanks, Tracey How about a little macro? Option Explicit Sub testme01() Dim myColWidths(1 To 256) As Double Dim iCtr As Long With ActiveS...

User IDs should not be case sensitive in Dynamics GP
Making User ID's case sensitive is contradictory with the User IDs/User Logins for the operating system - Windows. This new, undocumented feature in R9.0 is already causing confusion especially if a combination of upper and lower case (ie 'Mary' not 'mary' or 'MARY') is used for professional results on reports. With R8, users had enough trouble remembering their IDs now they have to be case specific. This is one feature that does not increase productivity nor security. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the...

separate columns
Hi, I am having a little problem and I was wondering if any of you coul help me with it. I have two columns, A (Definitions) and B is empty. Column A has several words in a single cell, I was wondering if ther is a way to put only the first word from cell A1, for example, in cel B1. Thanks. Regards, Marco -- Message posted from http://www.ExcelForum.com Hi Marcos! Try: =LEFT(A1,FIND(" ",A1)-1) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "marksuza >" <<marksuza.16lqli@excelforum-nospam.com> wrote in message news:ma...

Move Column Data into a row
Hello, I have text in a column that I want to move into a row instead, is this possible without typing it all over again? Thanks -- avidcat ------------------------------------------------------------------------ avidcat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29659 View this thread: http://www.excelforum.com/showthread.php?threadid=493706 Copy the data and then do a Paste Special using Transpose. Rgds, ScottO "avidcat" <avidcat.202gsm_1134625201.0378@excelforum-nospam.com> wrote in message news:avidcat.202gsm_1134625201.0378@excel...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

how to put(repeat 7000times) the word "available" in a column
I have 7000items in excel worksheet and I want to write the word "available" in the end of each line, please help me Hi select your 7000 lines by typing the range in the name box (little box to left of formula bar above the letter A) e.g. h1:H7000 and press enter this will select all the cells for you now type (don't click anywhere just type) available and press control & enter this will put it into each selected cell. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "eksel" <eksel@discussions.microsoft.co...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...

Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several charts. I have a wide sheet of paper coming off of a paper machine of varying widths. This paper is wound up on a "reel".I have a scanner that measures the weight of the paper on the reel, and each scan gives me a 600 point array. I can calculate where the edges of the paper lie within the array, no problem, and plot the whole thing very nicely. The problem is that the paper then goes into a re-winder, where the wide sheet is cut into smaller sheets, that make up "rolls". I can calculate the sta...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

XML Serialization in dynamically loaded assembly
I am serializing/deserializing a class (Class1) using the XmlSerializer object in the System.Xml.Serialization namespace in .Net 2.0. Class1 has some strings and ints and two lists of other simple serializable classes (Class2 and Class3) Class1, Class2 and Class3 are defined in an assembly (Assembly1) which is loaded by another assembly (Assembly2). When I statically reference Assembly1 in Assembly2, serialization and deserialization work correctly. When I dynamically load Assembly1 into Assembly2 using 'Assembly.Load', serialization and deserialization do not work. I get t...

Convert data from rows to columns
I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: tom.smith@madeup.co.uk Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / tom.smith@madeup.co.uk) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns....

How to create a column styled blog in Word
I want to use word to create content for a blog that looks like a magazine with 2 columns - how would I create a column blog ...

Putting formula into column from macro?
How do I load a formula into a column from a macro? I tried: ActiveSheet.Cells(6, 6) = "=SUM(H:H)" But it doesn't work..... Also, is there an object that returns the number of rows in a sheet? So I can loop until I reach that number? Suggestion...Please! Samuel, ActiveSheet.Cells(6, 6).Formula = "=SUM(H:H)" Rows.Count will return the number of rows in the sheet, but it is likely that you really mean the number of used rows. Sub Test() Dim i As Long For i = 1 To Cells(Rows.Count, 4).End(xlUp).Row Cells(i, 3).Value = "Filled in by the macro" Next i End Su...