Remove Ranges Bounded By Blank Rows
Jul 24, 2007
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.
View 9 Replies
ADVERTISEMENT
May 15, 2006
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 ?
View 6 Replies
View Related
Aug 16, 2013
I have data in column A separated by blank rows. I want to copy ranges between two blank rows, and paste it to sheet named "mega" so that each range is in its own column.This is code I'm working now.
Code:
Sub QuickSet2()
Dim rng1 As Range
On Error Resume Next
Set rng1 = Cells.SpecialCells(xlCellTypeConstants).EntireRow
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Copy
Sheets("mega").Range("A1").End(xlRight).Offset(0, 1).Paste
Else
MsgBox "No constants found"
End If
End Sub
View 6 Replies
View Related
May 20, 2009
I currently have the code below for a button that will unhide last 175 rows in my worksheet. I now would like to have it hide the rows that are blank in in the range (C7:BL206) looking from the bottom up. For example, if there were values in any of the cells (Columns C up to BL only) in row 190, the macro would only hide rows 191 thru 206.
Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub
View 4 Replies
View Related
Dec 15, 2012
share a code to remove BLANK ROWS.
I have data on ROW#1, #5, #10...etc,etc.. I want to remove rows in btwn (which is blank) Instead of doing manually
View 2 Replies
View Related
Nov 29, 2006
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
Next ws
End Sub
View 3 Replies
View Related
Nov 29, 2007
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.................
View 2 Replies
View Related
Apr 15, 2014
Pivot tables. I have a dataset from which I make a pivot table. In row labels I have 4 different columns of data below each other.
Level 1
Level 2
Level 3
Level 4
In some cases the data only has three levels, for example:
Earth
Europe
Germany
In other cases it has four levels of data:
Earth
Europe
Germany
Berlin
If I make a pivottable it will give 4 levels for all items. In the first case I mentioned, the data will state:
Earth
Europe
Germany
(blank)
Is it possible to remove the (blank) row as it does not provide any useful information..
View 3 Replies
View Related
Jun 28, 2013
Im working on a macro that i need to delete any row that contains 0 or blank in a dynamic range that starts at H23.
View 1 Replies
View Related
Aug 27, 2009
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.
View 9 Replies
View Related
May 9, 2008
Need Macro (code) to copy specific cells from a sheet into a new sheet in a desired format. I have an excel file which has data set in 20 rows. Each row has multiple fields. At the end of each data set there is a blank row. I want to capture specific data fields for each data set (e.g. Name, Bank no. etc) and put it into the respective heading. This process needs to be repeated for all the data sets.
A sample sheet is attached for better explanation. Sheet 1 has the raw data and sheet 2 has the required output. The no. of data sets shows in sheet 1 is just a sample. In actual it would be a large no. of such data sets.
View 9 Replies
View Related
Dec 4, 2007
I want to select a range in a spreadsheet and then set it as the print area. the last column I want to be selected is contains the text data "ABC" in row 1 and is the only column with data "ABC" in row 1. The last row I want to select contains the text data "DEF" in column 1 and is the only row in column 1 containing the data "DEF". Basically I want to select the range [A1:column("ABC")row("DEF")] and then set it as the print area.
View 2 Replies
View Related
Nov 29, 2006
VBA code to remove all named ranges in a workbook that contain "#REF" in the reference. I have some legacy workbooks and some have hundreds of invalid named ranges, it's painstakingly slow to delete them manually, a macro to wipe out the ones with #REF in them
ie:
Names in Workbook:
Print_Area
Refers to:
='I:ReportngAlain[buckets.xls]#REF'!$U$774:$AC$811
This one has the #REF in the reference, I'd like ones like this go be wiped out.
View 9 Replies
View Related
May 18, 2008
I need a listbox or combobox in an excel form whos list is created from 2 ranges. for example first name is range a2:a500 second name is rangne b2:b500. the list box should list both coloums in a single list and return the row number. better still if it does not list blank ranges. i have never used a list or combo box before so please explain in terms a simplton can undrstand
View 7 Replies
View Related
Aug 12, 2013
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.
View 5 Replies
View Related
Jul 15, 2008
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.
View 14 Replies
View Related
Jan 25, 2009
s/s is 325501 rows deep. Down one column are values but these are seperated by blank cells.
I need to omit the blank cells so that all the values appear together, but keep them in the same formation (not sorted).
View 6 Replies
View Related
Jan 25, 2009
s/s is 325501 rows deep. Down one column are values but these are seperated by blank cells. I need to omit the blank cells so that all the values appear together, but keep them in the same formation (not sorted).
View 4 Replies
View Related
Nov 17, 2011
In Sheet1 cell A1 has concatenate formula and the data source is linked from sheet "DataInput". Sheet1 cell A1 has multiple data and there are blank lines within the cell. I would like to remove these lines so it looks clean without any gaps. So basically its looking like this right now
Harry
Andrew
Joe
Apple
Billy
I want it to look like this but i am having a difficult time because this cell contains a formula
Harry
Andrew
Joe
Apple
Billy
A formula or something that would fix this problem automatically.
View 1 Replies
View Related
Nov 13, 2006
Is there a way to remove blank cells from a list?
Example:
A
1
3
4
5
5
Result:
1
3
4
5
5
View 9 Replies
View Related
Nov 14, 2006
I need to remove blank cells from a list of cells. I need to do this through a formula or a macro. I can't use Filter or Sort. Example:
A
1
5
5
2
3
Needs to look like:
1
5
5
2
3
View 2 Replies
View Related
Aug 30, 2007
I've attached a sample file.
Really need to remove the (blanks) in all my headings.
Download sample and let me know if you have any ideas.
View 3 Replies
View Related
Aug 13, 2008
I'm working with fragmented text inserts within my worksheet.
How can I make this:
,..A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P...
1,.............. Cell filled with text
2,.................................................Cell filled with text
3,.................................Cell filled with text
4,.........Cell filled with text
5,.................................................Cell filled with text
6,.......................Cell filled with text
7,.......................Cell filled with text
8,.......................Cell filled with text
9,...........................................Cell filled with text
look like this:
,..A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P...
1,Cell filled with text
2,Cell filled with text
3,Cell filled with text
4,Cell filled with text
5,Cell filled with text
6,Cell filled with text
7,Cell filled with text
8,Cell filled with text
9,Cell filled with text
Basically, I'm trying to create a macro that will remove the blank indented cells of each row, and return the cell (with text) to column A.
View 5 Replies
View Related
Nov 23, 2011
Any way to cycle through two ranges and fill in blank cells. The worksheet has a "header"(A2:H2) that bascially determines how the rest of the sheet will be filled out. This header is subject to change if one of the headers is changes(see PLAN, and TRAN CODE in example).
PLANSSNDATEFUNDSRCTRAN CODEITEM CODEAMOUNT (CASH)56016999-99-9999FBLNK01699161.00022.00033.0060988999-99-9999fBLNK0150595.00026.00037.00
The SRC and AMOUNT(CASH) fields will always be complete. What I'm trying to do is cycle through AMOUNT(CASH) and if the field is not blank to then cycle through crng lookig for blank cells. If the cell is blank it should copy the cell above. This is where I'm headed but it will cycle through the same crng(A3:H3) every time.
Public sub Line_Check
Dim ccell As Excel.Range
Dim crng As Excel.Range
Dim hrng As Excel.Range
Dim hcell As Excel.Range
'skip A1, A2(add code to validate headers)
[Code] ........
View 5 Replies
View Related
Nov 14, 2006
I have workbook that has questions on column K and L that NEED to be answered. When the user closes the spreadsheet I need the macro to create a pop up that says "unfinished Workbook"
So I need a macro that tells the user to finish answering the questions on K and L -
View 5 Replies
View Related
Oct 3, 2012
I have a column of cells, some contain text and some do not. The cells which contain txt or are left blank could change on a daily basis.
I'd like Excel to look at the column of cells which contain the data and populate the information into a list on another worksheet without the blank cells, can I do this?
How it looks now: Sheet 1, cells A1:A6
Henry
blank
blank
James
blank
Bob
What would be sweet: Summary data on Sheet 2, cells A1:A3
Henry
James
Bob
View 4 Replies
View Related
Feb 2, 2008
I have spreadsheet that displays many blank cells. I would like all the information from this sheet (example below) displaying on another spreadsheet but without the blank cells.
On the example below I've shown how I want my sheet3 to look. (Cells B19:I22)
The sheet where I want to remove the blanks is Sheet2 B2:AE367
Is this possible using array formulas or macro?
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1=BCDEFGHI1RedYellowBlueOrangeBlackGreenWhitePurple205/01/2008 27/01/20083 21/04/2008 4 31/03/2008 5 15/03/2008 617/01/2008 03/07/2008 7 22/08/2008 8 9 10/05/2008 25/09/2008 10 09/06/2008 28/02/200811 12 11/09/2008 1307/05/2008 12/11/2008 14 01/07/2008 15 16/10/2008 16 14/10/2008 17 18 19RedYellowBlueOrangeBlackGreenWhitePurple2005/01/200821/04/200831/03/200815/03/200803/07/200814/10/200825/09/200827/01/20082117/01/200810/05/200809/06/200822/08/200816/10/2008 12/11/200828/02/20082207/05/2008 01/07/200811/09/2008 Sheet2 [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
Jun 18, 2014
In my current sheet, I have a button which pastes the contents of the user's clipboard into cell A20. The data that is being pasted is a simple, single-column range of data that may or my not contain blank cells. When this button is clicked, the user's clipboard should already have data.
Some of these pasted ranges may go from A20:A40, A20:A60, or even A20:A73. The point is, the length of the pasted information is variable.
I am looking for VBA code which can look at the recently pasted range in A20:AX, find blank cells, and remove them. Here is my current code (very simple), which is only the paste function:
VB:
Sub admin_btnPASTE()
' paste_align Macro
' pastes data
On Error Goto Whoa
[Code]....
View 5 Replies
View Related
Jan 10, 2014
I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).
Concatenating the cells works except that I end up with 20 commas for two strings of info.
Just so you understand I may have this:
Mary red blue yellow purple
Rob blue purple
Trey yellow
and I want it to look like this
Mary red, blue, yellow, purple
Rob blue, purple
Trey yellow
How should I go about this?
View 14 Replies
View Related
May 21, 2013
I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.
To Buy List.xlsx‎
View 2 Replies
View Related