Consolidate Rows That Have Formulas In Them But Blank With Macro
Apr 3, 2014
I am looking for some VBA programming that will allow me to use 1 button to consolidate rows (delete them). The problem I am running into is that the rows I want to delete have formulas in them so I can use "find all blank cells & delete" type of macro.
Capture.PNGCapture1.PNG
Attached are a couple of brief images on a small scale what I am looking to do.
I want a user to be able to have this sporadic list of choices, consolidate them so the are all one right after each other, then export to a word file for editing later on.
View 1 Replies
ADVERTISEMENT
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
Sep 1, 2009
I have several lists that I need to be able to print out periodically.
The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.
I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").
Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.
I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.
I have attached 2 screenshots of my 'print lists'.
P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?
View 9 Replies
View Related
Sep 26, 2013
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
View 2 Replies
View Related
Apr 22, 2014
I am looking for a macro that will copy the row above it, to include formatting and formulas, however not the actual contents. I need the row that's being copied to be hidden from view, which is where the problem stems from. The macro is also copying the 'Hide' formatting and I want to exclude that. This is the current macro I am using:
[Code] .....
View 2 Replies
View Related
May 20, 2014
I am attempting to consolidate multiple worksheets into one. The consolidation works fine on worksheets that have data however, as in my example file, there are worksheets which are presently blank (empty of data) - save for the header row.
The problem:
On consolidation, the header row from the "Empty" worksheet becomes a row in the "master" worksheet that is created. How can a worksheet without data be ignored on consolidation?
View 2 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Feb 15, 2014
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
View 1 Replies
View Related
Dec 28, 2011
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
View 5 Replies
View Related
Aug 2, 2014
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
Nov 11, 2007
I have 2 speadsheets, I want to copy rows of Formulas from Spreadsheet 1 and copy them into Spreadsheet 2 but convert them into Values.
Is it possible to create a button on Spreadsheet 1 and assign macro to do exactly what i want? I also want the macro to look for the first empty row in the database so that the information being copied are on the next row and not overwritten.
View 10 Replies
View Related
Apr 25, 2009
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
View 8 Replies
View Related
Jul 13, 2009
I was searching through this site and found this macro ...
View 9 Replies
View Related
Mar 30, 2013
Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .
Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).
Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).
E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
View 7 Replies
View Related
Sep 8, 2009
I have about 10 rows of data that have columns of information describing computer hardware and pricing. There are 10 rows, because each row represents a different country and different tax rates and pricing changes per country. So each column of a row has a specific formula to calculate information for that row's country information. All these 10 rows of formulas are kept as a reference at the bottom of the sheet.
At the top of the sheet I need to have a drop down menu to choose a country and then it will automatically copy and paste the row of that country to the current row at the top.
Is there any easy copy/paste macro for such a thing?
View 9 Replies
View Related
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
View Related
Aug 5, 2008
I have three worksheets with a large amount of data on them (~60,000 lines). The column titles and layout are exactly the same. I need to either 1) Get all of the data into one worksheet so I can create a standard pivot table, or 2) Analyze all of the information using a multiple consolidation range pivot. When I try to copy the data from one spreadsheet to another I get an error message stating the copy area is a different size or shape from the paste area, even though it is not. When I try to create a pivot table using multiple consolidation ranges the fields returned are Column, Row, etc instead of the data fields I would expect (the column titles - exactly the same in each worksheet).
View 6 Replies
View Related
Nov 13, 2012
I would like VBA code to delete all rows where there are blanks in Col B
View 2 Replies
View Related
Dec 13, 2007
I need to write a macro that will successfully delete blank rows. I also need to write a macro that will successfully delete duplicates. The duplicates are numbers and they do constantly change.
View 9 Replies
View Related
Mar 3, 2008
I am able create a macro using the find function to find the next blank row, but I would like to have it select a range of rows down that I can then delete. Each time I run the macro the next blank row may be different thant the last, so it can't be a set number of row numbers each time. I could also possibly use the print area function if it would be easier.
Here is what I have so far, what is in red is where I need it to vary from next active blank row down to R2001C14, and then delete all the active blank rows ...
View 9 Replies
View Related
Jun 12, 2008
I need a macro to delete blank rows from row 2, but excluding the last 4 rows where there is blank rows between the data
See example below where the rows containing blank cells after #2 in column A must not be deleted ...
View 9 Replies
View Related
Mar 23, 2007
I would like code to consolidate weekly hours worked, on each project, for each person. I therefore wish to end up with 1 row per person, per date, showing their GRAND Total Hours worked in column P (across all projects, and across all tasks). So, in the example below, everybody's rows would stay as is, apart from Mike Jones's, which I would like to be consolidated into one row, showing a GRAND Total Hours worked of 16.
View 3 Replies
View Related
Jan 8, 2008
I'd like to consolidate rows with same value in one column to return total value of all the rows in another column. I am not very good with VBA so I was hoping if there is an easy non-vba way to do this. I've attached spreadsheet sample.
View 2 Replies
View Related
Mar 12, 2008
I have a report that has the option to download the data to an excel file, but am having trouble with the last portion of my macro to re-sort the data into something useful. Basically, I have all of my data in a single line, except for some "oddball" records that have an extra number in the row below (which I need). I did a quick macro record of what needs to happen with each one of those "oddbal" records, but I'm completely baffled as to how to get this to repeat every time the macro "sees" something in that second line. I've tried modifying the saved macro in the attached Excel file the following code (the If loop), but I'm getting syntax error that I cannot seem to resolve.
Sub Macro5()
Cells.Select
Selection. AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
If range "J" Selection.AutoFilter Field:=1, Criteria1:="<>"
Range.currentrow.offset(0,9).Select
Selection.Copy
Range.offset(-1,1).Select
ActiveSheet.Paste
End If
End Sub
View 3 Replies
View Related
Dec 11, 2008
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
View 8 Replies
View Related
Mar 8, 2009
I've added a button with a macro to delete any blank rows in a worksheet.
Cell A3 is completely blank and A6 has an IF formula which returns "". In my macro it copies the whole worksheet and then paste special values, to take away the formula in A6 and then deletes the blank rows but it still does not delete row 6.
View 4 Replies
View Related
Jul 30, 2009
I'm trying to find a way to delete blank rows that contains formula. I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:
ColA
AAA
(blank)
(blank)
BBB
(blank)
CCC
After entering above values in sheet1, here's what sheet2 would look like:
ColA
AAA
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
BBB
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)
CCC
What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:
ColA
AAA
BBB
CCC
Is it something possible?
View 14 Replies
View Related
Sep 14, 2012
I recently found this macro on these forums for deleting rows where all of the cells are empty.
Code:
Sub RemoveEmptyRows()
On Error Resume Next
With Range("B300:B1000")
[Code]....
View 9 Replies
View Related
