Autofill Until Non-blank Cell Macro

Jan 10, 2009

I have searched the forum a few times and haven't seen an exact example of what I am trying to do, so I hope this isn't a duplicate.

I am looking for a macro to copy a cell and autofill it down the column until it gets to a non-blank cell. When it reaches that cell it needs to then copy THAT cell down until it reaches another non-blank cell... this could happen just a couple times, a couple dozen times, or only the very first time.

So for example:

In cell B2 there is text "Data1"
In cell B13 there is text "Data2"
In cell B25 there is text "Data3"

I would like the macro to search column B to find "Data1", and then copy it down the column until it reaches B13, then copy "Data2" down until B25, where it would then copy "Data3" down until the end of the document. The location of the data in column B varies, and the number of cells with data in column B will vary as well.

View 9 Replies


ADVERTISEMENT

Autofill Macro For Variable Cell Data

May 13, 2006

I'll do what I can to explain this mess I want to clear up...I have a series of excel reports I have to download and work thru daily and I need to see if there is a code to autofill down thru a column, based on multiple statements throughout the column..here goes:

f1 msp
f2 msp
f3 blank **
f4 mct
f5 mct
f6 mct
f7 blank **
f8 cci
f9 blank **

Regardless of the actual f cell, I always will need to fill the Blank ** cell w/ the values from the previous cell....Hope this makes sense...& thanks in advance....
have_a_cup@cox.net

View 5 Replies View Related

Autofill Macro: Filling For Range Whilst There Is A Value In Cell

Nov 25, 2008

I recorded one for an autofill series which basically came out as

View 4 Replies View Related

Autofill Blank Cells

Jul 8, 2009

I have a column of names with some spaces e.g. A1: BROWN, A2:empty, A3:empty, A4:SMITH, A5: empty, A6:JONES etc. All the empty cells have the same name as the one above i.e. A2 and A3 should also read "BROWN". A5 should read "SMITH" and so on down the column. Is there away to create a formula that does this automatically. I've tried a simple if command in a duplicate column which looks at the first column and tests for a blank - if it finds one it enters the name from the cell above - but this only works once and some of the empty spaces need to be filled more than once e.g. A2 & A3 above.

View 2 Replies View Related

Macro To Autofill With Custom List Based On Value Of Current Cell

May 25, 2012

I have an Excel file for work that has the following:

Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)

What I have currently is that if it says:

John Doe 15:00 5

It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.

I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.

Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.

I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.

Here's how it looks when I'm ready to start the macro:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a

[Code] ......

And I need it to look like this:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d

[Code] ........

View 7 Replies View Related

VBA - Autofill To First Blank Adjacent Column?

Mar 5, 2014

I want to write some VBA code that simulates the double click of the square in the lower right of a selected cell. When you double click it, it fills every cell in column A until the first blank in column B. I was able to find this code searching around:

Code:
Selection.AutoFill Destination:=Range("A10:A" & Range("B10" & Rows.Count).End(xlUp).Row)
Selection.Copy

The issue with the above is that it copies the data all the way down to the LAST row in column B with data.

I want to have the macro stop when it finds the FIRST blank cell in column B.

Just as an example, the last row of data in column B is 100, but in row 67 the cell is blank, so I want it to stop at row 66.

View 2 Replies View Related

Excel 2010 :: Autofill Entering Data Against Blank Cells

Jan 23, 2014

In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g

X X X Z
X X X Z
X X X Z
X X
X X X

Now this happens

X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z

How can I stop this as I need to enter different data in the next section?

View 1 Replies View Related

Autofill Vertical List Horizontally With 2 Blank Cells In-between Each List Item

Feb 14, 2014

I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.

View 2 Replies View Related

Autofill In Macro ..

Dec 29, 2008

I am working on automating our truck payment system. When the macro copies the data over to the output printout sheet, it will only copy the information once, even though there are 15 lines that it should be copied to. When I originally recorded the macro, I used the auto fill option to fill the information for all 15 lines. However, when I run the macro the auto fill doesn't work for me.

View 8 Replies View Related

Autofill A Macro

Jan 29, 2010

I currently have a worksheet that, using a Form Button, generates a new worksheet that formats column widths, titles and also creates a file list of all files on my hard-drive along with date created and the full file path.

Each file has all of its information in one row.

From the original button that initiates the whole process I need the code to create a delete button for each file in the list using, I presume, the kill function.

The delete button would need to sit in column 'L' starting in cell '5' and autofill down. The kill function would pull the file path of the file to kill from the full file path displayed in column 'K', again starting in cell '5'.

I already have the code to genereate the file information (path, date created etc). I literally just need the code for the creating the multiple form buttons and assigning the macro to each button.

View 9 Replies View Related

AutoFill In Macro

Jan 2, 2007

code?

View 9 Replies View Related

AutoFill Macro

Jan 10, 2007

The following code works fine for what I want to do. I want to have a range a5:g5 copied and pasted on down to g200 - as you would do if you selected and draged the selection box on down to paste it. The problem is I want to avoid the ".select".

What solution whould I use?

Worksheets("GTopLists").Range("A5:G5").Select
Selection.AutoFill Destination:=Range("A5:G200"), Type:=xlFillDefault

View 6 Replies View Related

Macro That Will Go To The Next Blank Cell

Jul 11, 2007

I am looking for a macro that will go to the next blank cell in the column starting at B3 and if none are found going to the next sheet(sheets are january-december). The twist is that I need it to change sheets when there is nothing in column A(which has dates and times in it) then go again to the next blank cell in the following sheet starting at B3.

View 10 Replies View Related

Run A Macro When A Cell <> Blank

Feb 19, 2009

I have a macro that runs when the user clicks on a button. I want it to run when a particular cell has data in it - after the user selects an item from a drop down. How can I make the macro trigger based on a cell being non-blank?

View 9 Replies View Related

Autofill Columns Macro

Mar 29, 2014

Problem: The following code autofills columns O:P beyond the last row of data in column H.

Range("O3:P3").AutoFill Destination:=Range("O3:P3" & Range("H" & Rows.Count).End(xlUp).Row)

View 3 Replies View Related

Autofill Macro Monthly

Jul 14, 2008

I want to do is set up a macro to autofill into a new column every month. My data is linked to another excel document so that is where it is pulling the data from. Currently I have data in columns monthly from Dec 2001 until June 2008. I just want a macro that will automatically add in the next month, so in this case July then August the following month ect.

View 10 Replies View Related

Autofill Numbers Using Macro

Jan 21, 2010

I have recorded a macro and it is working fine

My query is while the macro is running a column is inserted and numbers are autofilled

View 14 Replies View Related

Macro To Autofill Columns

Oct 14, 2011

I need with the below statement. I need the statement to autofill columns b6:y6 down as long as there is data in column A6 down.

I am hitting a wall here

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/14/2011 by Oasis Group
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Range("B6:Y6").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B6:Y" & Lastrow)
Range("B6:Y" & Lastrow).Select
End Sub

View 3 Replies View Related

Macro To Encode In Last Blank Cell Up To Last Used Row

Jun 24, 2014

I have a worksheet that contains data but column E has blank cells. i want to automatically encode on the last blank cell in column E up to the last used row.

I am using the ff. code to select the last blank cell in column E:

Range("E1").End(xlDown).Offset(1, 0).Select

I am using the ff. code to select the last used row:

ActiveSheet.UsedRange.Rows.Select

but it doesn't work.

In my excel file, all i want to do is to encode "ECASE-UNUSABLE-UPD" in cell E61:1549 , wherein E61 = is the last blanck cell & E1549 = is the last used row.

View 1 Replies View Related

Macro Goto Next Blank Cell

Oct 19, 2009

I have a spreadsheet where I had a new column on a daily basis. I am trying to create a macro to automate some parts of this. I would like to select the next blank cell on row 1. So in this instance, cell M1, however the following day it would be N1. This is the part I am unsure of how to do. After this I will be doing a vlookup to add the new values, which I should be able to sort out no problem.

I have seen people using COUNT and OFFSET to select the last cell +1, however I am unsure how to link to two together.

View 9 Replies View Related

Find Non Blank Cell And Run Macro

Jan 23, 2010

I am try to write a bit of code which will find the non blank cells in column H (Range H4:H24) and when it finds a non blank cell make column C in that row the active cell and then run a macro. Once the macro as been run i would like it to look for the next non blank cell.

View 5 Replies View Related

Macro To Specify Blank Cell / Columns?

Mar 28, 2014

I extract data from a database and export it to excel, the data has A-E Columns.

Then I run a macro to perform calculations based on columns A-E,

The macro results in creating columns F - K. I have specified to run the macro on columns F-K.

Now in the future I would want to increase my database and add in more columns for example F-Hand this would directly effect my macro.

Is there a way to specify in the macro to conduct calculations that is "F-K" after "A-H" that is as soon as you hit a blank column/cell?

View 1 Replies View Related

CALENDAR Macro Autofill With Two Extensions

Aug 23, 2013

The macro should...

1. Autofill dates from the Start Date ("D5") for x amount of years. Autofill begins in Range("C10")
2. Point out the THIRD 'wednesday' of every month with a "1" in column D.
3. Point out the second last 'weekday' of every month with a "1"in column E.

View 3 Replies View Related

Absolute Range - AutoFill Via Macro

Jul 1, 2009

In a macro - how can you copy a cell and keep a data range absolute?

In the statement below, the range denoted by: DATA!RC[-52]:R[829]C[-52] is D2:D829. When I use the macro to Autofill using the following:

(Selection.AutoFill Destination:=Range("BG2:BG220"), Type:=xlFillDefault)

the range progresses. I need to have the range as an absolute.

Sheets("Report").Select
Range("BG2").Select
Selection.FormulaArray = _
"=SUM((DATA!RC[-52]:R[829]C[-52]=""APPOINTMENT"")*(DATA!RC[-55]:R[829]C[-55]>=Report!RC[-1])*(DATA!RC[-55]:R[829]C[-55]

View 9 Replies View Related

Position Independent Autofill Macro

Feb 4, 2010

I need a macro that can autofill irrespective of where the source cells are. Like, this is what I want to do:

Select two cells containing a descending order such as 50, 40. Hit the hotkey and the macro should autofill the same way as double clicking the bottom right corner of the selection.

The problem is that the two source cells can be anywhere in the sheet, and autofill range isn't fixed. Essentially, I just want a keyboard shortcut for Excel's own Autofill function.

I want this macro to be rid of the mouse when I'm working on excel (it really slows me down to use the mouse every time I want to autofill).

View 9 Replies View Related

Leaving The Cell Blank But Allowing The Macro To Run

Mar 15, 2007

How can I have my macro run in its cell and still allow the cell to be blank? Say I have a number, 0.99, in the cell and I want to delete it and leave it blank. I can either push "Backspace" or the delete key and it still leaves zeros.

If I push the space bar it stops the macro (which I do not want to happen) and leaves the cell blank because I added a space.

View 12 Replies View Related

Macro To Find Next Blank Cell In A Particular Column

Jul 15, 2009

I am trying to write a macro that will do a bunch of stuff then go to the next blank cell in a particular column.

The rest of the code for the macro is irrelevant I just don't know how to code it to find the next blank cell in the column. It could be anywhere from cell A2 to A1000000. Basically I want the macro to select the cell that is next on the list to enter data into.

View 8 Replies View Related

VBA Macro For Filling Blank Cell In Whole Column

Mar 1, 2012

I need a VBA macro for filling the blank cells in whole column "D" with some conditions.This condition work for whole column.

ABCDCN
EA
Polymer

DE
MA
Polymer
Medium
CN
EA
Polymer

Fill the blank cell in D column with "Easy", if Column A is "CN" and column B is "EA"
Fill the blank cell in D column with "Medium", if column A is "DE" and column B is "MA".

After Run the macro:
i.e
ABCDCN
EA
Polymer
Easy
DE
MA
Polymer
Medium
CN
EA
Polymer
Easy

I was used "if" condition but I can't able to run.

View 2 Replies View Related

Recording Macro So That It Fills Next Blank Cell

Oct 29, 2013

I'm trying to record a macro that is using vlookups to get data from another data sheet, this data sheet will update each month - the old data moving back a column (so the new data is filled into the same column each time).

However, on the sheet where i am recording the macro, i need the data to fill in the next column each time.

I have recorded the below:

Sub Colourants()
'
' Colourants Macro
'
'
ActiveWindow.SmallScroll Down:=-30
Range("AK3").Select

[Code] .......

How do I get it so it doesn't record in cell 'AK' all the time but the next empty column each time it's run (however, it will always run from row 3).

View 1 Replies View Related

Autofill Summary Macro As New Worksheets Are Added

Jun 11, 2014

Trying to write a macro so that my summary worksheet will auto populate when new sheets are added and filled out. I want the PO#, Quanity, Date ordered, Vendor, Subtotal, Tax and Total all to transfer from worksheets like F001 to the summary sheet. Yes there is only one F001 sheet filled out but that number will rise into the hundreds as the project progresses. No idea where to begin when writing this macro.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved