VBA or Macro to auto fill a selected range

Hello everyone,

Here is my problem.  I have a spreadsheet with four columns, which are
variables nested within each other.  What I am currently doing is going
through a range, finding the blank cells, filling all the blanks with
the value of the  non-blank cell above it, and filling these blanks
until I reach a non-blank cell.  Simply put, all I am doing is clicking
on the cell's handle and filling the blanks (it only fills the cells
down until it finds a cell with a value).  

I am tediously going through each range of cells, autofilling down, and
working from right to left.

There must be an easier way where I can select the range of cells, run
a macro, and it does the autofill for me.

Any suggestions?

Thanks in advance,

Brock


-- 
Btibert
------------------------------------------------------------------------
Btibert's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8559
View this thread: http://www.excelforum.com/showthread.php?threadid=397252

0
8/19/2005 3:41:11 PM
excel 39879 articles. 2 followers. Follow

2 Replies
705 Views

Similar Articles

[PageSpeed] 35

Brock,

A macro is actually VBA.  This is an unusual request, but try this:

Sub FillCells()
Dim MyRange As Range, cel As Range
Set MyRange = Range(Cells(1, 1), Cells(9, 4))
'Set MyRange = Range("A1:D9")

For Each cel In MyRange
  If cel.Offset(1, 0) = "" Then cel.Copy Destination:=cel.Offset(1, 0)
  Next cel
End Sub

Use the Set MyRange statement that's the most convenient.  Set it to your 
range, not including the bottom row.
--
SEarl Kiosterud
www.smokeylake.com

"Btibert" <Btibert.1u0r2a_1124467504.5167@excelforum-nospam.com> wrote in 
message news:Btibert.1u0r2a_1124467504.5167@excelforum-nospam.com...
>
> Hello everyone,
>
> Here is my problem.  I have a spreadsheet with four columns, which are
> variables nested within each other.  What I am currently doing is going
> through a range, finding the blank cells, filling all the blanks with
> the value of the  non-blank cell above it, and filling these blanks
> until I reach a non-blank cell.  Simply put, all I am doing is clicking
> on the cell's handle and filling the blanks (it only fills the cells
> down until it finds a cell with a value).
>
> I am tediously going through each range of cells, autofilling down, and
> working from right to left.
>
> There must be an easier way where I can select the range of cells, run
> a macro, and it does the autofill for me.
>
> Any suggestions?
>
> Thanks in advance,
>
> Brock
>
>
> -- 
> Btibert
> ------------------------------------------------------------------------
> Btibert's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=8559
> View this thread: http://www.excelforum.com/showthread.php?threadid=397252
> 


0
someone798 (944)
8/19/2005 5:01:56 PM
I truly appreciate your help.  Doing a simple test, it appears as if it
does the trick.

While it may be a strange request, my file that I am working with
contains over 5000 rows of data, therefore, the process I was using
earlier was quite tedious.

Regardless, I really appreciate your assistance and thank you for
taking the time to assist.

Thanks,

Brock


-- 
Btibert
------------------------------------------------------------------------
Btibert's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8559
View this thread: http://www.excelforum.com/showthread.php?threadid=397252

0
8/21/2005 7:42:06 PM
Reply:

Similar Artilces:

triggering a macro by workbook_close or workbook_open
I've been getting some help on this, and I have worksheets that I'd lik to veryhide on closing, and unhide on opening if macros are enabled otherwise it displays a warning worksheet. what I have been doing i just hiding it all before I close it, but I'm thinking what if someon else uses my template, opens it with macros, closes it, then open without macros, then it won't work anymore, and they will be able t see it. so I created the following code, but it isn't working.. please help. Private Sub Workbook_Open() ActiveWorkbook.Unprotect password:="******" Shee...

E-mail Address "Auto Complete" in Outlook 2003
In at work we have Outlook 2003 on our machines connecting to an Exchange server. When using Outlook there, if it recognizes a partial address, it pops up with a list of recently used addresses matching the characters entered so far. Last night I installed Outlook 2003 at home where it talks to a couple of pop server. Unlike Outlook at work, my instance at home does not "auto complete" addresses. I am at a loss to find a setting to do this. Is this feature only available when Outlook is working with an Exchange server? Or am I missing something? Thanks! Don Open Outlook an...

How do I get auto fill to do decending numbers in a column?
I am trying to get excel to do decending numbers from 50 to 1 in a column and no matter what I try it still will not do it. What am I doing wrong? Hi one way, type 50 in the first cell, underneath it type 49, then select both cells, now go to the fill handle on the 49 and drag down. Cheers JulieD "oesqueen" <oesqueen@discussions.microsoft.com> wrote in message news:082D4471-39F1-41E1-B50C-5420B18526BF@microsoft.com... >I am trying to get excel to do decending numbers from 50 to 1 in a column >and > no matter what I try it still will not do it. What am I doing...

Excel macros -looking for good resources
I am using Excel 2000 and am wondering if anyone can recommend good places to learn about building macros, best practices, etc. Thanks, Joe From Norman Harker *********************** Here's a bit of a summary collation of Excel resources posted originally in response to a question on how to become an Excel Expert. Perhaps the very best resource is yourself! Few experts have gained their in-depth knowledge other than by banging their heads on the keyboard trying to work out solutions to problems. It's sometimes frustrating but even blind alleys teach you things and successes are ...

returned mail
--------------------------------------- There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <servername #5.5.0 smtp;552 MS-Office file containing VBA macros found inside of the email> --------------------------------------- I cannot figure out why this error continues to come up. This just started after loading the patches from last Tuesday, and it does not happen with all office files. These files probably do have macro's, but they had no problems sending before last Tuesday's patch day. A googl...

Macro with actions on variable/dynamic pathnames
Ian - or whome it might interest :) I try to make the macro (below) a bit more flexible to adopt dynamic folders by using a formula in my workbook "UserList.xls" - where - range A1.value contains thie dynamic filpath as expression - exmpl: "c: \Aplications\Report\" - which more easily can be used to direct the macro to take actions and search for files in various folders - as C:\variable\variable\" I cant find out how to change the hardcoded expression: "c:\Aplications\Report\" with formula-based expression:"C:\variable \variable\" Best Regards Sn...

Changing Year in Date Range
I am using a crosstab query for selecting data for a date range spanning 12 months over two different years using a "Between & And" date range e.g Between #01/04/07# And #31/03/08# For the following 12 month period I can manually change the Year numbers in the above criteria. my question is: is it possible to change or select the Year number without having to change it manually in the query design view by say a parameter box? Have you considered using this --- Between CVDate("4/1/"&Format(Date(),"yyyy")) And CVDate("3/31/"&Format(Da...

Multi Selection in Access
I'm trying to design a work order form that would contain a field that I can assign employees to the work order. On any given work order I could have from 1 employee to 30 employees....I don't want to enter the same information for every employee....is there a way that I can select multiple employees in that field? I would like to eventually be able to query by employee.....I would also like to be able to print a report that will show me every employee that was on that job? Thanks for your help. I would suggest using a one-tomany relationship between workorder table and workorder-e...

DPM 2010 RC Tape selection
Has the order of selecting tapes changed in DPM 2010?? With 2007 it would always the highest numbered tape and work it's way down, but it seems that 2010 is doing it the other way...is this correct?? The selection order remains same, have you enabled Tape colocation? In such cases it may end-up using the same tape even if it is placed in a lower number slot. Can you please elaborate on the problems if it chooses a different order, were you using this as a work-around to force the next backup to go to a particular tape? Thanks, Praveen D [MSFT] This posting is pr...

Help screen for VBA in A2007
I have been trying to use VBA in Access 2007. I need the help function often and find it non-existent in A2007-all they give you is some useless macro items. Is there a way to get the the great help screen that you could get in A2000? Or, is there some other place on the web where you can enter something very general and get a list of possible answers? Google! Seriously, I agree that MS did something to 2007 help. Google is almost every developers best friend regardless of their programming language. For crying out loud, Google is a better search tool for this very for...

Auto fill part of a form based on a look-up table???
I have a form that will be utilized by several individuals. I want to make it easier to fill out by having a name/address/tel#/etc. look-up table to fill in most of the basic info based on a reference number. The idea is that 'if' they insert their reference number, most of the repetitive info would automatically be filled in and they could then make minor changes plus add the unique data. It seems like the filling in of the data would have to be triggered by the entry of the reference number. Any suggestions?? Bob Bob It all starts with the data ... If you are...

How do I keep text from automatically filling in another text box
When I fill in a text box, the same text automatically appears in another text box. I don't want that to happen. I want each box to be filled independently. Thank you. Click in the test box that you don'e want the text to continue and then click the "broken chain link." "Chicago" <Chicago@discussions.microsoft.com> wrote in message news:9C5E740A-1C10-4DAC-9B78-ED2898E9EEEE@microsoft.com... > When I fill in a text box, the same text automatically appears in another > text box. I don't want that to happen. I want each box to be filled > in...

Receipt doesn't print, only TransactionTables (no Tender) are fill
Hi everyone, we have a strange phenomenom here with our MS Retail V 1.0 Sometimes when we sent the Worksheet 250 (approx 15000 Items) to our stores and a sale transaction is booked in that time The transaction is recorded in the Transaction and TransactionEntry table but not in the Tender - nor in the journal. That leads to our Z-Report not beeing consistent and nor is our data transmitted to our ERP (SAP R/3 in that case). Is it a known problem with the 1.0 when doing a booking and the 250 at the same time? Any help is highly appreciated. Best regards, Chris ...

I don't know but I may need a macro?
I don't know but I may need a macro? This is a golf league There are the regular players and the substitute players On worksheet1, I enter scores for individual players and the substitute players. On worksheet2, the scores are transfered from worksheet1, and further calculations are made. Under perfect conditions, if no substitute players are playing, all the scores I entered on worksheet1, transfer to worksheet2, and all is right with the world, my work is done, all I have to do is print. However, If a regular player is "MIA", that transfers to worksheet2 a "MIA",...

Multi-Select PickList
I know how to create a picklist attribute on the contact record but is there a way to create a multi-select, i.e. I can select multiple items from the list? not as far as I know. Use checkboxes or realtions hope it helps -- ______________ closing and rating answers is a good habit and helps the community. please make it so "milonedp" wrote: > I know how to create a picklist attribute on the contact record but is there > a way to create a multi-select, i.e. I can select multiple items from the > list? Hi, a good alternative is to do this by individual programming...

How to turn off auto save on forms?
Hi All, I need to be able to have the database not auto save when entering in new information on a form. I would like for there to be a prompt for them to save the information. As of now when you enter in new information and close the form it auto saves, and this is what I am trying to avoid. Can anyone please help with this? Thanks On Jan 7, 8:14 am, Michelle <Miche...@discussions.microsoft.com> wrote: > Hi All, > > I need to be able to have the database not auto save when entering in new > information on a form. I would like for there to be a prompt for them to &...

How to do auto save in Excel VBA?
Hi, How can I do auto save in Excel VBA, say every 2 minutes? Thnak you. GL Are you trying to control the Autosave addin via VBA or trying to write your own save program? If you just want that functionality... What version of excel are you using? If you're using xl2k or lower, you can install the AutoSave addin (you may need the CD if you didn't install it previously). If you're using xl2002 or higher, then AutoSave was replaced with AutoRecovery (mostly used if excel/windows crash). It's not the same as autoSave, though. If you still have your old version of autosave....

Outlook 2000 Selecting a user profile to use when Outlook starts
I am able to do this using Outlook 2003 (see http://office.microsoft.com/assistance/preview.aspx? AssetID=HP052423751033&CTT=4&Origin=CH063565691033) but can it be done with 2000 WITHOUT setting up a new computer user? Right now Outlook 2000 brings both my email account into the same inbox. What I want Outlook 2000 to do when it opens is to first ask me which profile I want to load. I have looked everywhere but can't seem to find a solution other than creating another User on my computer and then signing on as them. This is a hassle though because I have to keep changi...

Excel 2003 - vba
HI All: I have this working in one workbook, but when I transplant it into another, it fails to function as expected. Issue: I would like to check my current workbook to ensure that a sheet does not exist, before trying to create it. In a UserForm, I allow the user to input a sheet name that they would like to import. Upon their depressing the "AddListBut" on the userform (ListForm) it checks to ensure the user put something in the Listbox "NewListName" then check to see if the worksheet already exists. Public ShtName as String Private Sub AddListBut_Click() If ListForm...

After running a macro the first character, entered, is swallowed
Hello, after running of any macro sometimes the first character, entered, is swallowed in Word 2000 (on Windows XP SP3). Unfortunately I connot reproduce the exact circumstances for this strange behaviour - therefore the expression 'sometimes', but I can reproduce it on different computers. What may be the reason for this strange behaviour; how can this problem be resolved? Thomas Wiedmann We would have to see the code of the macro(s) to have any chance of helping you. It sounds like perhaps the document does not have the focus after the macro is run. -- ...

Auto send birthday greetings
Hi, I would like to auto send birthday greetings via outlook from calende entries. (Office / Windows 2k os and suite) Any ideas? / Stephe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Query Date Range
How can I use Between/And to prompt for date range that includes the start and end date? Hi, Design a select query for your table. add all fields to the design grid. In criteria box of the date field write this Between #BeginningDate# And #EndingDate# and run the query to find results. JB "karenfocus" wrote: > How can I use Between/And to prompt for date range that includes the start > and end date? My current criteria is in a query date field and is written Between [Enter start date] And [Enter end date] but when I run the query from the first day of the month to the...

Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP
Hi, I would like to enter values into a Chart from a Named Array(Range). The Array should not be an actual Range, but a virtual array based on logic applied to a Range. In my case picking values in ColumnB based on values in ColumnA Ideally I woud like to write something like: Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1) and then make a Chart out of it. (I actually want to do something more complex: a variable-width bar chart using an X/Y scatter, w/o using Bullen's method) My problem is that ONE offset is applied on the whole array, and not DYNAMICALLY to the different items. Need...

Auto-updater problem if Office not on boot partition
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I prefer to have my applications installed on a different volume than my boot partition. Office won't let me choose a different folder/partition during the installation. But, once it's installed, I can move it to my "apps" partition and it runs fine. However, the auto-update just launched and, I guess, because the applications are not on the boot partition, the update just hangs when it gets to the "Destination Select" step. It never enables any of the partitions (volumes) on my computer. All...

Convert range of numbers to letter
Hello. I hope this makes sense. I have a spreadsheet that has columns with numbers ranging from 1 to 100. I would like to convert them to letters i.e. 90 - 100 = A 80 - 90 = B 70 - 80 = C 60 - 70 = D and so on Is this possible? Thank you JB "JB" <badspam@awyway.com> wrote in message news:OUlxDBVPGHA.3728@tk2msftngp13.phx.gbl... > Hello. > I hope this makes sense. > I have a spreadsheet that has columns with numbers ranging from 1 to 100. > I would like to convert them to letters > i.e. 90 - 100 = A > 80 - 90 = B > 70 - 80 = C ...