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
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View 2 Replies


ADVERTISEMENT

Insert Row At Specific Location And Auto Fill Formulas From Above

Apr 11, 2007

What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).

Sub ChkDates()
Dim c As Range
Dim DelRng As Range
Dim ArcRng As Range
Dim i As Long
Dim l As Integer
Application. ScreenUpdating = False
Worksheets("Report").Activate
For i = 60 To 8 Step -1
Set c = Cells(i, 33)
If IsDate(c) Then ..............

View 9 Replies View Related

VBA Delete Rows And Insert Formulas

Jun 23, 2013

delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.

Account Number

211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row

211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

View 2 Replies View Related

Insert Rows Based On User Input But Then Auto Delete Them At The End

May 25, 2013

I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.

The code I have for the inserting the rows (from searching through forums) is:

Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9

[code]....

View 1 Replies View Related

Macro To Insert Two Columns With IF Formulas Into Worksheet?

Dec 13, 2011

Writing a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.

The new Column H would be

"=IF(G1>=Q#,G#/Q#,0)"

And the new Column I would be

"=IF(G#>=Q#,MOD(G#,Q#),G#)",

Where # represents the row number.

View 3 Replies View Related

Delete Columns And Rows Based On Cell Fill Color

Jan 31, 2014

I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.

Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.

View 2 Replies View Related

Compare Columns & Insert / Delete Rows

Feb 22, 2008

I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.

There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.

View 9 Replies View Related

Macro Used To Insert Rows And Copy Formulas

Jul 13, 2009

I was searching through this site and found this macro ...

View 9 Replies View Related

Macro To Insert Auto Sum Function In Various Rows With A Column

Sep 4, 2009

I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.

Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.

Just Column E - I need an autosum in the "empty row 1's)

23.00
15.00
6.00
3.00
empty row 1
empty row 2
6.00
3.00
empty row 1
empty row 2
6.00
25.00
3.25
6.32

View 9 Replies View Related

Macro To Delete Rows That Fill Certain A Certain Condition

Jun 25, 2007

How do I have the marco delete rows if the O column = a certain cell.

For instance my O column has a bunch of colors

Cell N1 list one color (red) and I want the macro to delete the rows that contain the color from N1 in column "O".

View 9 Replies View Related

Delete Columns, Increment Alphanumeric Text, Insert Rows & Sort

Feb 18, 2008

I am having to write a vba code for a survey data captured code online.

I am attaching the code and the vba module that is suupose to do

following things

2) dELETES THE LAST 3 COLUMNS

3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)

4)Inserts new row at top from Q1 TO Q11

5) Sorts data on Q6 (Faculty/Staff)

Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....

View 9 Replies View Related

Auto Fill Formulas

May 20, 2008

ColA-ColB
A1 - B1
A2 - B2

Let's say B1 has a formula- =A1
b2 has formula = =A2

I want to copy all the formula in ColB
As it goes down in Column B, it should be =A2 at 2nd row, =A3 at 3rd row, =A4 at 4th row.

Reason being I want this formula to activate when the cell in Column A is filled. If the cell in Col A is empty, The formula in Col B should be blank!

Example:

ColA-ColB
13/01- Jan
13/12- Dec
29/06- June
30/07- July

View 9 Replies View Related

Add Multiple Formulas In Row & Auto Fill Down

Mar 15, 2008

I am trying to do can’t really be recorded, at least i don’t think it can. I have a report that I run off and after I loaded it in Excel I then have to add in five columns at the end of the sheet. These are always in the same columns: R,S,T,U,V.

These are the formulas I have to then input
R =IF(G2="no invoices",A2,"")
S=IF(I2="Match",A2,"")
T=IF(I2="Sent to AP",A2,"")
U=IF(I2="Force Settled",A2,"")
V=IF( COUNTIF($R$2:$U$10054,A2),A2,0)

Note for column V, the range R2:U10054 would depend on how many rows is in the report, its just in this example the report does have 10054 rows. The columns would always stay the same. I would then copy the formulae down for the number of rows that is in the report and filter column V by entries that equal zero to give me the rows I am looking to analyse. Is this the type of thing that can be written as a macro so that everytime I have a new report open in Excel I can just run the macro and it will do all of this for me?

View 3 Replies View Related

Copy Formulas Or Using The Auto Fill Need To Have The Count Inc By 5 Instead Of 1

Apr 17, 2009

I have the following formula:

=AVERAGE('sheet 2'!C7,'sheet 2'!D7:Z7)+AVERAGE('sheet 2'!C9,'sheet 2'!D9:Z9). When I copy it down to the next cell the valules need to increase to 12 & 14 respectively.

View 12 Replies View Related

Macro To Extract/insert Data Then Delete Rows

Jul 25, 2008

I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3  Transactions for  CUSTOMER ID: 0000CUST7   Sales        4        5        6Product summary for Vehicle ID   00001080       7        8Product Description  Transactions Quantity9        101 Unleaded  3 57.60  GL11        12Hose summary for Vehicle ID 00001080       13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I am trying to get the data into a more convenient format for analysis. I need a macro that will:

1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).

2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded  3 57.60  GL111081 Unleaded  6 84.70  GL121122 Unleaded  5 47.00  GL131182 Unleaded  8 95.80  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 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

Fill Blank Rows With Formulas

Jan 4, 2007

I want to copy an active cell in row 2 (I dont want to fix a cell as it can be any cell at any time). I want to copy it all the way down to a cell where the next row after it, is completely blank on all the 256 cells.

View 6 Replies View Related

Fill Empty Table Columns With Required Formulas

Jan 17, 2008

I'm trying to write a macro for extreme value analysis. I need a macro which will do a number of things! the attachment should explain it.

View 2 Replies View Related

Auto Copying Column Formulas To New Rows

Feb 14, 2014

I'm working on a spreadsheet that tracks project accepted and done over a course of time. I am expecting the data to eventually reach the thousands in a year's time. I have several formulas in specific columns that I need to replicate as the user enters new data into the next row.

While it is easy to copy paste the formulas from the cells above, we are looking to save time by having the sheet do this automatically which also prevents the user from accidentally deleting/modifying the said formulas. I am also looking to save file size which is why I am considering doing this in VB. An example of a formula that I need to move down into the next column is:

=IF(ISERROR(VLOOKUP(C2,Tables!A:B,2,FALSE)),"",VLOOKUP(C2,Tables!A:B,2,FALSE))

I am new to VB but would like to gain some understanding on how this works.

View 2 Replies View Related

Auto Fill The Rows After Particular Selection From Dropdown List?

Dec 16, 2012

auto fill the rows after selecting a particular option from a drop down list. For Ex., In Drop down list we have Cool drinks and Ice cream as two options. If i select Cool drinks the respective columns should be filled with brands like, Coke, 7 Up etc...

View 3 Replies View Related

Insert Rows (and Copy Formulas) Using Command Button?

Feb 5, 2014

I have a simple command button in my worksheet that will insert a blank row and copy formatting, but not the formulas. How do I adjust the code so that the formulas are copied to the new blank row? This is currently what I have:

Private Sub CommandButton2_Click()
Sheets("WAWF Track").Range("A9").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
End Sub

View 5 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

Auto Fill Within Macro

Sep 14, 2009

my macro simply activates a cell and all i want it to do is to auto fill downwards!
its intended to autofill down for about 100 rows but just cant figure it out!

but keep in mind the cell is always going to change so it cant be fixed

i.e this wont work:

Selection.AutoFill Destination:=Range("C95:C659")
Range("C95:C659").Select
ActiveWindow.SmallScroll Down:=513

because i need it to be autofilled from the CURRENT active cell only!

View 9 Replies View Related

Insert & Fill Missing Rows Within Sequential Dates & Times List

Sep 17, 2009

I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.

I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.

Here is my problem:

I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)

Example of data (Where "/" delienates column seperation):

Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain

6-1-06 12:15 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:30 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:45 am / 45 / 35 / .0001/ 95 / 5 / 7 / 3 / 0
6-2-06 6:00 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-2-06 6:15 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0

So what I need to do is this:
1) Find which data times are missing
2) Add the appropriate amount of rows in between where the missing data would be
3) Add the correct dates to the new rows
4) Add "N/A" to the columns that have no data

You can see that my date and time are formatted in mm/dd/yy hh:mm

I found this on the website and was trying to modify it to my needs:

Sub InsRow()

Dim c

View 9 Replies View Related

Auto Fill Doesn’t Work, But Trying To Avoid Manual Entry Of Rows.

Dec 31, 2009

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)

Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!

However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?

To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?

View 3 Replies View Related

Macro To Insert Formula And Fill Down To Last Row?

May 21, 2014

I need a macro that will insert this formula: =Trim(IF(B3="","",MID(B3&", "&B3,FIND(" ",B3)+1,LEN(B3)+1))) into column C starting on row three through the end of the spreadsheet information.

I typed this incorrectly when I first posted. I need it to fill down into only column C not the end of the spreadsheet.

View 14 Replies View Related

Auto Insert Rows From Another Column Based On Count

Jun 9, 2014

I would like to insert rows into column A from column D based on the count values and without affecting subsequent values in column B and C. For example. Count of a value in column A =1 and count of that same value in column D=5, THEN 5-1 (count of D - count of A) insert 4 rows in column A (shift cells down) without affecting data in column B AND C. Basically making the counts in column A and D equal.

COLUMN A
COLUMN B
COLUMN C
COLUMN D
G250001

[Code]....

The above example should look like this -

COLUMN A
COLUMN B
COLUMN C
COLUMN D

[Code]....

View 6 Replies View Related

Delete Rows Without Affecting Formulas

Sep 28, 2009

delete rows without affecting formulas

In general, when you write a formula:

In Cell A1:

View 7 Replies View Related

Delete Rows With Ivalid Formulas

May 17, 2007

I have a problem with deleting rows that contains invalid formulas, these formulas are generated randomly daily in rows that contain no data and refer to empty cells in the same row.

Is there a code to delete rows an empty row in that by data it is empty, but not so because there is a formula?

currently this code of mine doesnt work on the zero data but formular-ed cell.. cause counta on the cell gives me 1

Dim i As Long, EmptyR As Long
EmptyR = ActiveSheet.UsedRange.Rows.Count
For i = EmptyR To 1 Step -1
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next i

View 9 Replies View Related

Delete Rows Based On Fill Color

Aug 21, 2008

i found a link to my problem in the subject title below,

http://www.ozgrid.com/forum/showthre...t=45580&page=2

The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!

My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.

View 9 Replies View Related







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