Macro - Fill Columns D-N Only Up Till The Row Number In Column A

Feb 19, 2014

I have a control chart macro which is being used for patient doses of a medication. Right now my macro automatically fills columns D-N down to row 100. However, I would like it to stop at the last row of data entered in column A. I have attached the code related to the formulae a

Sub Control_Chart_1()
' Control_Chart_1 Macro


View 1 Replies


VBA Code To Automatically Fill Down And Apply Formula Till Last Cell

Aug 22, 2012

I am using this code and it works fine:

Dim MyData As Range
Dim MyResult As Range
Set MyData = Range("E1:E1000000")
Set MyData2 = Range("F1:F1000000")
Set MyResult = Range("J4")
Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"

Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.

View 2 Replies View Related

Create Macro That Filter Date In A Column From One Week Previous Till Current Date

Mar 14, 2014

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"

View 8 Replies View Related

Count Number Of Days Till Next Birthday?

Dec 21, 2012

If a cell contains a birthdate, how do I count the number of days till the next birthday? The date is entered, as an example "1/1/2013".

View 5 Replies View Related

Column Fill Colors In A Column Chart When Some Columns Are Hidden

Jul 13, 2014

When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?

View 1 Replies View Related

AutoFill Till Last Used Column

Jan 8, 2009

I have a spreadsheet with a varying number of columns with data.

Now I like to copy the header over it till the last column that contains data.

If I used the recorded VBA the range is stated in letters (In the sample below column K but this can be any column).

What is the best way to code it in such a way that one gets the headers till the last column used?

Selection.AutoFill Destination:=Range("A1:K1"), Type:=xlFillDefault

View 9 Replies View Related

ADDING A ROW Till Certain Column Only

Apr 15, 2009

Can I have a macro code to insert a row from column A:K.

I have data in column L to IV which I want to stay as it is. i.e. for example when a row is inserted from A:K, that row should not extend beyond Column K.

View 9 Replies View Related

How To Fill In IF Function Across Columns Changing Column Ref Only

Jan 16, 2014

My formula in sheet "A" in cell D7 is this


What I would like to do with it is fill in row D7 to AH7 with this formula without changing the row reference in the IF function, only the column reference.

On the Activity_Log worksheet, my data is listed from cell C2:C32, which is selected from a drop down list, and F2:F32, which contains the formula =(E2-D2)*24.

I've attached a sample : Sample_MonthlyReport.xlsx

View 9 Replies View Related

Fill Multiple Number Series In Same Column?

Jul 10, 2014

I need to fill multiple number series in the same column. Example:



View 3 Replies View Related

Select First Cell In Every 20th Column Execute Autofill Till The Last Row?

Apr 18, 2013

I try to do something like this

Sub autofill()
Selection.autofill Destination:=Range("A1:A1048576 "), Type:=xlFillDefault
End Sub

but i need to select first cell in every 20th column and then execute autofill till the last row that excel provides. I can only do this manually but I have for now 125 columns to fill or to write some monstrous code. Unfortunately I do not have the knowledge to do this in a smart way and I guess there is an elegant way to solve this problem.

View 1 Replies View Related

Select Column C And Delete Blank Entire Rows Till C300

May 4, 2014

i want to delete entire blank row from column C2:C300 i mean if i have data c2:c100 then c101:c300 delete entire blank rows

View 4 Replies View Related

Macro To Fill Cells Of Rows Based On Two Other Columns

May 28, 2014

Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.

First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.

Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.

The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.

See here Fill Cells.xlsx

View 1 Replies View Related

VBA To Insert Formula And Then Fill Down Number Of Rows In Reference Column

Apr 8, 2014

I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:

Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"

[Code] ........

View 3 Replies View Related

Fill Down But Have Column Letter In Formula Change And Not Cell Number

Dec 30, 2009

i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.

View 9 Replies View Related

Macro To Fill Cells For Number Of Times With Value Divided

Jun 3, 2014

I need a macro to do the following

Assume A1 has 100 & B1 has 20

The macro should divide 100 by 20 & then fill 20 for number of times I get as the answer starting from B3 9 in this case its 5 times

E.g.: B3 , B4 , B5 , B6 , B7 should be filled with 20

If A1 has 200 & B1 has 50

B3, B4 , B5, B6 should be filled with 50

View 1 Replies View Related

VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill

May 28, 2009

I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.

Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"

UserGRP_MAcro Macro
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlUp
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View 2 Replies View Related

Macro To Fill Column?

Nov 29, 2011

I need a macro to perform the following:

On column A, from row 2 to 22, I have on each cell, an equal to (=) formula which links the result from another sheet in the same work book.

I need a macro that begining on Column C, everytime the cells in Column A changes from 0 to a number, displays the number on column C and when cell in Column A goes back to 0, Column C retains the number. Next time Column A displays a new number, then Column D is filled, next number Column E and so on.

Example: Lets suppose that cell A2 shows 1234, then cell c2 will show 1234, once cell A2 goes back to 0, cell C2 remains 1234. Then when cell A2 shows a new number 5678, then cell D2 will display the new number 5678 and if A2 goes back to 0 cell D2 will retain the number 5678 and again, once cell A2 displays a new number 1589, then E2 will display 1589, etc, etc. This could happen as many as 20 times (up to column V) before erasing all columns and beginning all over again.

View 8 Replies View Related

Asymmetric Fill (fill Out Column B Referencing Column A, But Only Incrementing By 1 Row In A Every 2 Cells In B)

Feb 1, 2010

Is there some easy way to fill out column B referencing column A, but only incrementing by 1 row in A every 2 cells in B?


Column A:
A1 = 1
A2 = 2
A3 = 3

Column B (I would like to fill this, referencing column A):
B1 = A1
B2 empty
B3 = A2
B4 empty
B5 = A3

View 4 Replies View Related

Macro For Spreadsheets With Different Number Of Rows And Constant Number Of Columns?

Mar 1, 2014

I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.

Sub City2()
' City2 Macro

Cells.Replace What:="POMPANO", Replacement:="Pompano Beach",


View 12 Replies View Related

Macro To Auto Fill Dates In Column

Jan 14, 2009

I have a startdate F1 and an end date G1. Is there a macro that can list all dates by day within that range into column I7:down.

View 9 Replies View Related

Macro To Fill Column With Relative Formula

Nov 21, 2007

I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is
=SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?

View 9 Replies View Related

Counting Number Of Filled Columns According To Another Column

Dec 19, 2012

I have this data lets say id denotes a particular house and the multiple entries of the same house are for different members of a house. Now i want to calculate the total no of people who earn in a particular household i.e if a cell is filled or not.

idB C


View 2 Replies View Related

How To Compare Data Of Two Columns And Show Number From A Third Column

Apr 16, 2014

How can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample

View 4 Replies View Related

Transpose Column To Rows - Referencing Columns By Number

Jun 19, 2012

I'm trying to create a macro that transposes data from columns to rows.

My source data is laid out so Column A and B contain item identifiers, and then the header for Column C to Column S contain dates (March, April, May, etc) and the row data below contains quantities for each month. This is on Sheet1.

I need my end data (on Sheet2) to have the item detail in Column A and B, the quantity in Column C and the Date in Column D. If there is a date that has no quantity then it should be skipped.

Sample Source Data (Pipes added for clarity, they aren't in the actual data)

Part |Description |June 4 |June 11 |June 18
A | PartA | 5 | | 12
(Please note June 11 has no quantity)

Sample Destination Data:

A | Part A | 5 | June 4
A | Part A | 12| June 18

Below is the start of the code, obviously I have a ways to go before it's fully functional but hopefully you get the idea how I'm trying to attempt this. Where I'm having issues right now is referencing columns by number. Is there a way to do this so I can do Column = Column + 1 to advance columns to the right? Or any better way to address this all together?

HTML Code:
Sub ConvertToRows()
Dim ReviewRow, ReviewRowEnd, PasteRow, ColumnNumber As Integer
ReviewRow = 2


View 1 Replies View Related

Linking 2 Columns To Fill 2 Columns In Dropbox List?

Mar 3, 2014

It's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)

On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:

On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.

View 2 Replies View Related

Multiple Columns Which Need To Be Combined With Some Columns Not Having All Cells Fill

Apr 30, 2014

I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.

I have create a spreadsheet with the data and the result that I typed into the "result" column.

View 3 Replies View Related

Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

View 3 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4


View 2 Replies View Related

Find Row With Keyword And Then Use Row Number Macro To Hide Columns

Nov 4, 2013

I have a workbook with several sheets that have basically the same template but some have many more rows that others..

I am using the following code to hide columns based on cell value to "tidy it up" for printing.

Dim i As Long
For i = 170 To 2 Step -1
Cells(39, i).EntireColumn.Hidden = Cells(39, i).Value = "N"
Next i

I would like the VBA to determine what the row number is based on the row header e.g. "Prioritised Courses", rather than having to maintain the code each time new rows are added.

I would put it at the top or way down the bottom, but multiple (even less excel skilled) users will be using the "hide columns" functionality.

View 6 Replies View Related

If Fill In A Number Open Word And PDF Doc That Is Attached To That Number

Jun 3, 2014

I have on the first tab a screen where you can fill in the name of the .doc or .pdf file.

That works fine, but now I would like that when I fill in a number and click on test, it opens te .doc and .pdf file that is attached for that number.

If you take a look at the pdf file you will see a second and a third tab.

First column is the number they need to fill in and the second column is the name of the .doc or .pdf that needs to be opened with that specific number.

How to link it and open both files for that specific number.

But not every number has a .doc as well as a .pdf.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved