I have an excel sheet with about 200 000 data rows informations. I will need to find a way to insert 3 blank rows after each 500 data rows. I was thinking about a vba macro, but I do not know anything about that.
View 2 RepliesI 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].....
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
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.
i have this code which inserts blank rows in alternate rows,
Code:
Sub insertrow()
' insertrow Macro
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
For count = 1 To 20
If activecell.Value "" Then
activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
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.
Using excel's text to speech I've put together a basic spreadsheet.
[URL]
Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech
[Code]....
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedI have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon.
View 14 Replies View RelatedI have an excel sheet that has the A column populated with many different things.
I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.
So...
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Would turn into
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
I have 1006 records in an excel file and would like to erase 2 blank rows every 37 filled rows.
View 9 Replies View RelatedI have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
I can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
The number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:
(before paste)
A
B
C
(after pasting unknown number of rows)
1
2
...
n
A
B
C
Is there really no built-in way to do this?
I have been trying to delete about 86k rows in my table in a worksheet. It has been over 5 hours now and it is still running to delete. any better solution? or how long do i need to wait for the system to finish its work?
View 1 Replies View RelatedI have a range of numbers in a single column and I want to insert a blank cell or line below each cell in the range. Is there a quick way to do this, by not using VBA.
View 3 Replies View RelatedI have a very long excell sheet and I want to be able to have two buttons off to the side that allows me to filter and unfilter my page. What I want to accomplish is if the cell in column "A" is not populated then I do not want to see that entire row when I filter.
View 4 Replies View RelatedI have a worksheet that contains column A7 through M7. Row 7 contains the header. Currently I have a small vba procedure that inserts a blank row whenever the data in column G7 changes. Here is the code that does this.
View 14 Replies View RelatedI have a worksheet called "Online Post" and I need a way to do the following.
Check for two blank rows next to each other that have :a background and border, if true then do a Edit -> Clear -> All
So when the file is exported to HTML it doesn't include the blank rows.
I thought about using a marco that just deletes all the blank rows but I have blank row splitting up sections of the page.
I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.
View 4 Replies View RelatedIt effectively counts the number of non-blank (>0) rows in an array. This
formula is limited, however, in that every column requires a separate
statement. I would like to find a function that could handle an array of any
size with a single statement.
100
110
000
001
3=SUM(IF((A1:A4>0)+(B1:B4>0)+(C1:C4),1,0))
1=OR(A1:C1>0) for
every row in the entire array. Unfortunately, according to this document AND
and OR functions cannot be nested within SUM+IF statements:
Dim oRow As Range
Dim cNonBlanks As Long
For Each oRow In Range("50:80").Rows
If Application.CountA(oRow) <> 0 Then
cNonBlanks = cNonBlanks + 1
End If
Next oRow
I have a real problem with a file I'm working on. It has invoice numbers in one column, followed by payment milestones. In the row underneath, there is an 'x' to mark if payment was made in a particular zone, e.g.:
6010136113221/06/201005/07/201022/07/201016/09/2010XXX6010136113313/07/201030/07/201013/08/201014/10/2010XXX
The problem is that there are two rows with data, a blank row, then another two rows with data. I have thousands of rows and need a quick-fix to delete the blanks.
a VBA code to delete blank rows.
The current worksheet has data which is retrieved from other worksheets.
For example:
Current worksheet A1= Sheet1!A1
Current worksheet A10= Sheet2!A1
Current worksheet A20= Sheet13!A1
The range of this current worksheet is A1:F1287 and inbetween there are blank rows. The cells in the current worksheet are not technically blank, because each cell (A1:F1287) retrieves the information from the respective worksheet.
I would like to know of a VBA code to delete a whole blank row/-s (all columns of this row is blank) inbetween the range. Therefore, if there is a whole blank row, this row to be deleted and to go to the next row that shows information . In other words, instead of me manually searching and deleting whole empty rows; a VBA code for this task.
I was wondering if there is a way to add 4 blank rows after every non empty row.
I dont know how to code. And Im guessing thats the way to do it.
modified my code to have the data continue to the next row where it left off before jump to other sheets. The code below creates too many blank rows of all sheets (9213, 9316, 9501 and 9601).
After the code stops execute, I have to delete all the blank rows. This takes too long, approximately 5 minutes for each sheet...
How do I go about copying rows starting at A1 and counting down until there is a blank row in VBA?
I.e.
Row 1 - value
Row 2 - value
Etc etc
Then Row 13 is blank
I just want to copy all the data above the blank row, which I will then paste on another xls?
how to hide rows that are blank as a result of formulas in that row returning "".
I have rows between A10 and A80 that I need to test for blank as above an automatically hide them if they are "". I need to redo this each time I view the sheet
Be gentle, I am just a VBA newbie and havent quite finished reading Bill Jelens excellent VBA book purchased on this site.
I have 5 columns and 1000 rows of data,
All cells has formulas, resulting blank or value,
I need a formula to copy only the rows with values
building another column (list) with no blank cells
Is it possible without macro?