I am a CAD person, trying to edit a large spreadsheet for reading into CAD.
I need to merge 2 worksheets into a 3rd.
Then search and delete the blank rows. (There is only 3 columns in the document, but can be as many as 5,000 rows or more)
The Cad program does not like blank rows, so I have to delete them if users insert them, and there may be several in a row. (Users need only edit one of the worksheet, No. 2)
The 3rd worksheet must then be saved to a Tab-Delimited txt file in the same location on the network as the xls document.
I am able to select the worksheets and copy to a 3rd, but how to go to the end of the data, before merging the other data from the 1st worksheet into the third.
I'm in the middle of coding a macro to convert files. About 30 minutes ago, the VBA editor started doing something completely baffling. I was copying and pasting this code into the editor from Ozgrid - simple macro to delete blank rows.
Sub DeleteBlankRows1() 'Deletes the entire row within the selection if the ENTIRE row contains no data. 'We use Long in case they have over 32,767 rows selected. Dim i As Long 'We turn off calculation and screenupdating to speed up the macro. With Application .Calculation = xlCalculationManual.................
I 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
I'm trying to figure out the if conditions for this relatively simple problem. Basically, this is an import from a word document where the table strays onto a new page. I want to try and repair this with a bit of VBA.
This is what it looks like:
1: cell 1 |the content should all be in this cell 2: |but the import sometimes splits it into two 3: cell 2 |
Basically if and only if cell Ax is blank (in this example A2), then I want the rows to merge each cell and repair the table.
I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.
for example
aa bb
ww xx zz
rr
gg hh ii jj
would become
aa bb ww xx zz rr gg hh ii jj
I have over 30000 lines so doing it by hand is not an option.
Sub Remove_E_H_Ts() Application. ScreenUpdating = False Dim x& For x = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 With Cells(x, 2) If Left(.Value, 1) = "E" Then ' i need to add If Left(.Value, 1) = "E" or "H" or "T" Then .EntireRow.ClearContents End If: End With: Next x ' after clear contents how can i remove blank rows ? Application.ScreenUpdating = True End Sub i am trying to make this line of code work below....
If Left(.Value, 1) = "E" Or "H" Or "T" Then
then also after clear contents how can i remove blank rows ?
I currently have dozens of sheets within a workbook and require empty lines to be removed from each page. I already have a working code to delete the lines but I now need to be able to apply this macro across all sheets in one go. I have included a sample excel sheet with the ‘delete blank rows’ macro and also the code I have been trying to use (without success) to apply the macro across all sheets. The apply macro to all sheets I’ve been trying to use is:
Sub AllSheets() Dim ws As Worksheet For Each ws In Worksheets
Once a week I get a spreadsheet with 39 separate batches of data, only across 5 columns, and each batch is separated by a blank row. The data batches are not the same 'height' in other words some may comprise just 20 rows and others as many as 550 rows - also, each week they are different to the previous week.
I've little experience with Macros, and failed dismally in trying to do the following:
1/. Delete the rows containing the first batch of data - including the 'blank' row at the foot of that batch. 2/. Save the file in it's original name after deleting the rows.
Then I perform another task with the remaining data, before repeating the steps above
It sounded simple before I started, but my recorded macro (cringe) seemed to make the assumption that every time I wanted to delete the 'top' batch of data rows, it would be the same length or number of rows as the batch I had used to 'record' the macro - in recording the macro I used shift+end+down to identify the rows I wanted removed, but the macro seemed to think I wanted to delete (say) Row 8 to Row 278 - which was true of the 'height' of the first batch of data but none after that.
I have to download a report through SAP and have written some pretty basic stuff to automate the formatting process, however, i still have to manually remove blank rows. I would like to be able to add the removal of theses rows as part of the code, one of the main problems that i see is that i run this report every week and the empty rows are not necessarilly in the order every time.
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.
I want to print a worksheet where their are blank rows for future use between the main body of the data and the total row. I want to exclude the blank rows.
i have a wordlist containing about 0.5 Million words in col.A and Millions of meanings in Col.B. Now in Col. A there are many duplicates. I would like to make one cell of all duplicates in col.A and put their meanings together in B with seperator ";". Its very important that there are no duplicates in Col. B.
For example: Col. A Col.B w1 m1;m2;m3 w1 m1;m2;m5 w2 m23;m24 w2 m50
Now after running the macro the list should look like:
Col.A Col.B w1 m1;m2;m3;m5 w2 m23;m24;m50
The words in col. A are alphabetically ordered and all the duplicates are followed by each other.
Column M of Sheet 1 contains either "y" or "n". I'd like Sheet 2 to populate with data in cells A-D but only for rows where column H = "y". I can figure out the condition easily enough: =IF('Sheet 1'!M2="Y",'Sheet1'!A2,"")
Repeat for each column to display. But I'd like to suppress the blank rows for both display purposes and the possibility of using in a Word mail merge.
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 have a column which contains unique values and also blank cells between them. These blank cells are associated with the cells having value below them. e.g.
Suppose these above are 14 cells (A1:A14); I want to merge preceding set of blank cells with the values below them.... in this example I want to merge A2,A3,A4,A5 with value in A6...
Likewise A10,A11 merge with A12 to show value in A12.
Sheet 1 gives Store locations (approx 1000) and various types of revenue, (eg each Store Location may have many different rows of Revenue - Putney - Sales Putney - Sundry Income Putney - Donations Sheet 2 gives Type of Store Puntey - Charity
I want to merge the sheets so that I can see Store location, Store Type and Different Revenues. eg Putney - Charity - Sales Putney - Charity - Sundry Income Putney - Charity - Donations
Disclaimer: I know almost nothing about VBA or Macros. I started with them yesterday. I took the following code off Ron de Bruin's website. I am attaching his example spreadsheet. When you click on Test 2 it does everything I want BUT copy the header row over. The workbook I want to apply this too has 12 sheets++ which have the same exact columns and headers, but different row counts. Ron directed me back to his site and the following code:
I have 13 different worksheets with the same header columns, i would like to merge into one creating new work sheet. All the data in column A should keep on adding on the column A from all the worksheet and from column N going across. I have more than million records on each sheet
I have 447 worksheets in my workbook, I need to consolidate them into one sheet and transpose the data so that it goes into across rather than down. I really only need data in rows 19 to 40 but the data in 29, 30, 31 A to I needs to be in one cell.
I am trying to merge several imported worksheets into one worksheet so that I can build reports. In other words, sheets 1-3 are imported from other workbooks, then sheet 1, sheet 2, and sheet 3 (with the same header rows) will roll into sheet 4 with reports built from the merged worksheet. I understand how to import, but I need to merge the worksheets. Is there a macro to make this possible?
I have a spresdsheet with a few tousand rows that is updated daily. My objective is to remove duplicates from colum C keeping always the most recent (date on colum B). Sometimes collum C will have blank cells and the rows of said blank cells can't be deleted.
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.
I have this macro to go to a specific folder and open up all of the files in the folder and merge them into a worksheet.
I want to change it so the user can select the files to be merged.
Sub MergeFiles() Dim path As String, ThisWB As String, lngFilecounter As Long Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet Dim Filename As String, Wkb As Workbook Dim CopyRng As Range, Dest As Range
I am collecting data from 6 locations for the quarter and combining the information into a corp worksheet. Each quarter I need to copy information from 6 different files and paste it into the master file worksheet. The sheet I need to copy is named Substandard. The amount of information changes from each location (ie one may have 10 rows one 50 rows) so the number of the row to paste it into the master sheet changes. I would like a macro that opens the file Location1 copies the information from the substandard sheet opens the Master file pastes the information into the Substandard sheet closes the Location1 file, and repeats the process for all 6 locations. I would end up with all infomation in the Master file substandard sheet. I know how to manually record a macro to do this but the numer of the row to paste it into varies on the Master sheet depending on the amount of information from each location.
I've tried to combine rows, but can't seem to do it. I have a spreadsheet with over 18,000 lines of data, and I really don't want to go through each line to add information from another line and then delete that line. Is there a formula that can do this? I have attached a sample of what I have and what I need.
I am trying to merge rows of duplicate entries, say I have the following information in a excel spreadsheet:
Column A: Name Column B: First Line of Address Column C: Area Column D: County Column E: Post Code Column F: Tel Number
If I have the data above in my spreadsheet I want to basically search and look at columns B,C,D,E and F and then if there are any duplicates of these for that row anywhere on the sheet I want it to merge the Data for those duplicate as shown below:
A|B|C|D|E|F Dave, Mark | Rice Rd | Liverpool | Merseyside | L45 7HT | 6381754 Tony | Hill Grove | Runcorn | Merseyside | L78 9JU | 6527897 John | Lime Ave | Runcorn | Merseyside | L34 9HF | 7248853
I have managed to find a macro that is shown below however this macro only looks at 1 column and then merges the data based on that which is not what I want
