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].....
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
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.
how to split one large worksheet into several worksheets using VBA. The only criteria I have to go on in the large worksheet is the existence of blank rows between each chunk of data I want on it's own worksheet.
Please see the attached zip file. There are 39 chunks of similar (but varying in number of rows) data that are separated by 3 blank rows, so I want my workbook be split into 39 new worksheets. My goal is to get the data into list/table format so I can work with it more easily and export into Access etc.
Also, just out of curiosity, is it possible to convert a HTM file into CSV? The attached data starts life as a HTM file that I just convert to XLS
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A
B
C
D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
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 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.
I 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 the following macro that inserts a blank line when a field changes. This works fine. However I would like to use this in another spreadsheet that has several addition columns of unrelated data. Therefore I want to insert 1 (row of) blank cells only in the columns specified in the range. The range is 3 columns wide, E - G. I think I'm close, but ...
Dim myRow As Long
myRow = 3 'data starting row
Do Until Cells(myRow, 5) = "" ' the # is the column that changes which I want to trigger a blank insertion
If Cells(myRow, 5) = Cells(myRow - 1, 5) Then
myRow = myRow + 1
[Code] ..........
I have in column D 1200 rows of data. I need to insert a blank cell after every 10 rows. Is there a formula or code to do this?
View 9 Replies View RelatedI have a spreadsheet that I need to insert a blank row every other row and then merge that blank row. I can do this with the macro below. My question is that I only want the merge on each blank row to merge A:H. Example would be insert row 9 and merge A9:H9. Insert row 11 and merge A11:H11. and so on till the end.
Code:
Sub insertrow()
Application.ScreenUpdating = False
Rows("9").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Merge
ActiveCell.Offset(2, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
but this time, the numers of duplicates is variable:
before:
---A
1-6
2-6
3-6
4-1
5-1
6-9
7-9
8-9
9-9
ETC.
and after I want to:
---A
1--6
2--6........
I have this code which adds a blank row above the name "EV01_" where it appears. ...which works fine, but only works for half the rows.
[Code] ....
I want my macro to consider the # of rows of data on a spreadsheet and insert values into columns for those rows but then stop when it hits a blank one. For simplified example below, I have 3 columns in my spreadsheet. Row 1 has headers of Location, Status, and Effective Date. The first 5 rows of data under the headers have a value in the Location column (San Jose, Oakland, etc). When I run the macro, I want it to insert a value of "ACTIVE" in the Status column for each row that has a value in the Location column. Also, a single Effective Date value is stored in a cell elsewere in the spreadsheet, and I want the macro to insert that value in the Effective Date column for each row that has a value in the Location column. When it hits a blank row - row 7 in this spreadsheet - I want it to stop.
Location| Status | Effective Date
San Jose
Oakland
Austin
Houston
Phoenix
I currently am using an IF statement in the Status column fields to say if Location = blank, then blank, else "ACTIVE". That works, except that the # of rows populated in Location column could be 5 or 1,000, and inserting the formula that many times seems to quickly increase the file size.
I am trying to accomplish three things with the attached workbook. The first thing is the insert a blank row between the different part numbers, column "A" to make the sheet easier to read. The second thing is to highlight the data in light gray (A:E) leaving the blank row that was created empty. The third is to then sort the worksheet by data in column "A" to group like numbers together.
View 12 Replies View RelatedI am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
I want to insert blanks rows above rows that have the number 1 inserted in column C.
I about 60,000 rows in all, doing it manually takes forever because I have to keep waiting for Excel to push the rows down every time I insert a new row.
Is there an efficient way to grab all the rows with a 1 in column C and insert rows in one swoop? If not, I will settle for any way other than manually.
I have spreadsheet with data all over. I want a macro which identifies the first cell (in Column A) which has the text "BNY" and insert 6 blank rows above the text "BNY" (First text in the Column).
Once, it is done - the macro should also assign names to the last inserted row.
Column A - Should reflect "Bank", Column B should reflect "Field1", Column C should reflect "Field 2" and Column D should reflect "Field 3".
And after the names are assigned - It should also highlight the last inserted row in Yellow.
For Example:
Raw Data (Snap):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .....
Output (After Macro):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .......
Note: Split from: VBA to insert page breaks at a blank with varying blck size
I would like my macro to add empty lines to each pages not completely filled. (you know when it removes a block from a page, there's always some free space at the bottom of that page) Is it possible to add blank row (from a certain template on another sheet that I have) until it's full?
I have a spreadsheet containing hours logged against a specific project, all of which have a 'Week Beginning' date.
I would like a macro to insert two rows above a change of date (in the D column). How best should this be done?
It's basically a formatting macro I'm after, to group records by 'Week Beginning' date.
I've spotted a few similar requests, but none of them appeared to include the function to check for a change in date, from the cell above.
I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .
Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.
This is a reformulation of this post: [URL] ...........
I need to insert blank rows at the end of the each data segment to add totals for each individual. Is there a macro that I can use for this? I'm attaching an example file.
View 8 Replies View RelatedWe have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
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
I have a worksheet that includes the list of buildings in an area and the number of households in each building. I want to list the households on separate rows and to number them from 1 to x according to the total number I have. Is it possible to do this using a macro, if so, how?
View 5 Replies View RelatedI've created a macro to assign to a button that inserts a row above say row 10, then another macro to assign to a different button that inserts a row above row 20. My problem is, having clicked the first button a row is inserted above row 10 but of course row 20 now becomes row 21, so now I need the second button to insert a row above row 21 instead of 20.
Is there any way I can write a macro that remembers the old row numbers?
Example:
My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.
As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie: ....
I need a macro that will insert 11 rows before the word total. The word total will always be merged between col A&B but will not always be in the same row. It might be in row 5 one day and row 25 the next.
I have recorded a macro to insert six rows. I'd like to take this one step further and have the user enter the number of rows they want to insert. Right now the data is entered in A10:F33.
View 9 Replies View RelatedI've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.