Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down VBA help please
Imagine in Excel you have A1 = 1
A6 = 2 A11 = 3 This is for this weeks data, next week value 2 could be in A7 or A10 or A12 etc. I want to have the flexibility to fill down from e.g. this week from A1 to A5 and fill A6 to A10 will a 2 etc. How can I do this? I can't seem to get the range flexibility, thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down VBA help please
Try this idea
Sub fillnumbersmod() j = 1 For i = 1 To 12 Step 5 Cells(i, "a").Resize(5) = j j = j + 1 Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Imagine in Excel you have A1 = 1 A6 = 2 A11 = 3 This is for this weeks data, next week value 2 could be in A7 or A10 or A12 etc. I want to have the flexibility to fill down from e.g. this week from A1 to A5 and fill A6 to A10 will a 2 etc. How can I do this? I can't seem to get the range flexibility, thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down VBA help please
One interpretation
Assume the first value is in A1 and other values across the range A1:A500 and you want to stop filling at A500. Sub fillcells() Dim rng As Range Range("A501").Value = "A" Set rng = Range("A1:A500").SpecialCells(xlBlanks) rng.Formula = "=A1" Range("A1:A500").Value = Range("A1:A500").Value Range("A501").EntireRow.Delete End Sub the commands for A501 are there to insure the UsedRange of the sheet extends at least as far as you want to fill since specialcells doesn't work past the end of the UsedRange. -- Regards, Tom Ogilvy " wrote: Imagine in Excel you have A1 = 1 A6 = 2 A11 = 3 This is for this weeks data, next week value 2 could be in A7 or A10 or A12 etc. I want to have the flexibility to fill down from e.g. this week from A1 to A5 and fill A6 to A10 will a 2 etc. How can I do this? I can't seem to get the range flexibility, thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down VBA help please
Hi Don,
The gap from where "1", typically is in A6, yet the location for "2" could be anywhere below , e.g. in A7, A9 , A20 etc. After A6 the cells below are blank until there is a value of 2 in whichever cell it is. However If(IsBlank) works within Excel via the addition of a column yet I wish to "hardwire" this in VB. Any suggestions? Regards, Simon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down VBA help please
Hi Tom,
Thansk for your reply. The gap from where "1", is typically in A6, yet the location for "2" could be anywhere below , e.g. in A7, A9 , A20 etc. After A6 the cells below are blank until there is a value of 2 in whichever cell it is. However If(IsBlank) works within Excel via the addition of a column yet I wish to "hardwire" this in VB. Any suggestions? For example (1 uis normally static - actually in A6), the rest of the cells below are blank, due to a crosstab, until 2 occurs in a cell, then below the cells are blank until 3, etc, down to 6 currently. The reason for filling int he blanks is so that a filter can be put in place: A1: 1 A2: A3: A4: A5: A6: A7: 2 A8: A9: A10: A11: A12: A13: A14 3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill handle fill down alternative methods question | Excel Discussion (Misc queries) | |||
Erase fill print then bring fill back | Excel Discussion (Misc queries) | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) | |||
How to fill colour in Excel, it appers No fill in my computer? | Excel Discussion (Misc queries) |