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
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
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
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
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
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
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
Jul 13, 2009
I was searching through this site and found this macro ...
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 28, 2009
delete rows without affecting formulas
In general, when you write a formula:
In Cell A1:
View 7 Replies
View Related
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
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