Delete Range Of Data
May 20, 2009
Picture a simple spreadsheet (A1:C10). In column A, the word "delete" appears on every even numbered row (2, 4, 6, etc). Blank (no) data appears on the odd numbered rows (in Column A).
The following code was to sort A1:C10 by column A, bringing all the blanks to the top (now, rows 1 through 5) and "deletes" to the bottom (now rows 6 through 10). I then want to delete all the rows which contain the word "delete."
Selection.Find(What:="DELETE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A185").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("B184").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
My problem is that each time the macro is run, the word delete will appear on a different row. Can anyone suggest how to fix the above code so that it simply finds the word "delete" (after the sort), then does the equivalent of [END][DOWN], [END RIGHT] (to highlight the desired ranged to be deleted, then executes the delete command.
View 9 Replies
ADVERTISEMENT
Mar 26, 2014
I have excel sheet it contains 200000 records.I want to delete records from 50000 to 200000.Is there any shortcut to delete data.
View 2 Replies
View Related
Oct 11, 2009
I have a worksheet with 300 rows and I have a separate worksheet that contains graphs based on the data in the 300 rows.
The data is continually updated but stays at 300 rows. I add a new row at 301 and then I delete row 1.
I set the graphs up with a Chart Data Range of =GData!$B$1:$AB$300
As I add and delete rows the Chart Data Range reduces so after adding and deleting 3 rows the Chart Data Range is now =GData!$B$1:$AB$297
How can I get the Chart Data Range to stay at =GData!$B$1:$AB$300?
View 9 Replies
View Related
Oct 3, 2006
I have code in Worksheet_Change, and it works great when a user is filling in the data. When the user deletes data one row at a time, it also works. However, what winds up happening, is the user selects all their data in a range, and hits the "delete" key once, and then I get an error. How can I check whether or not the target is multiple rows and columns?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Row > 15 Then
If Target.Value < 1000 And Target.Value <> "" Then
'My logic goes here
End If
End If
Application.EnableEvents = True
End Sub
View 2 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Aug 24, 2009
I have set up a Purchases Journal as a database. The DB has 5 field names. The last row sums columns C to E. The Userform captures the info for a new record and posts to the DB. I am trying to delete the last row with the totals before the userform is displayed. I put the totals back with the OK button on the userform. There is a problem with the "Rows(LastRow).Delete" line of code.
View 2 Replies
View Related
Jan 30, 2009
I'm trying to clear all rows with a calculation error. The "N/A" Am I close? :
For Each cell In Range("MSLnew")
If cell.Value.IsNumber = False Then
cell.EntireRow.Delete
End If
Next cell
View 9 Replies
View Related
Jun 16, 2009
See attached workbook. If you hit the Export button (runs Export Macro), it creates a new workbook. Most of what I have in the code works fine except the following bit:
View 2 Replies
View Related
Jan 28, 2013
I need a macro to delete a specific range of rows. For example below:
UK
Manchester
London
Chelsea
Birmingham
[Code]....
Each line represents a row. If a cell matches "JAPAN" then the macro needs to delete all the rows relating to it (in this case all the cities that are based in Japan). So after running the macro my list will have:
UK
Manchester
London
Chelsea
Birmingham
[Code]....
View 2 Replies
View Related
Sep 23, 2013
I have 50,000 to 150,000 rows of data. Row M has data from 0001 to 9999. I need to delete all rows except the rows with values 2500 to 4999. I had this working with a similar and smaller spreadsheet awhile back using an advanced filter. In this case I need to delete the unneeded rows. I would imagine I could also achieve it with a VLOOKUP array and a lot of finagling but with this many records to repetitively
View 8 Replies
View Related
Jun 17, 2014
I have a sorted range from the highest to the lowest in range B8:B50000. I need to find the first cell with zero value and last cell with zero value in this range. Then the these rows are selected and deleted.
View 4 Replies
View Related
May 29, 2008
I have been researching how to create a macro (that will attach to a button), that will delete ALL Shapes and Pictures in a range of cells. So far I have only met with limited results. The Range can be hard coded into the script as it will not change. Does anyone have a script written already they would care to post? I am using Excel 2003.
View 9 Replies
View Related
Oct 3, 2008
I need a bit of code that will delete a row if a range in the row is all blank
ie, if all cells in range between I and AZ are blank then delete the row
I also need this to happen from row 5 downwards as top 4 rows are headers etc.
I have been trying to modify this piece of code but with no luck
Dim fn As WorksheetFunction
Dim rngDelete As Range
Set fn = Application.WorksheetFunction
Set rngDelete = Range("I1:AZ1")
NumberOfRows = Range("a10000").End(xlUp).Row
For x = NumberOfRows To 1 Step -1
With rngDelete
If fn.CountA(.Offset(x, 0)) = 0 Then .Offset(x, 0).EntireRow.Delete
End With
Next x
View 9 Replies
View Related
Nov 8, 2008
I inhereted a spreadsheet where the designer used range names for all formulas. i hate range names. is there any way i can delete all range names and have all formulas go back to showing cell references instead of range names? When i just go through and delete the range names, i get formula errors.
View 9 Replies
View Related
Mar 17, 2009
My data is in one column as follows:
ABC
(BLANK)DEF (BLANK)(BLANK)
What I want to do is to delete the blank cells and count how many not-empty cells.
The following sub works fine if I put the active cell manually in A1. However, the first range select always give me the "1004" error. Why? I don't have any clue.
Range("a1:a5").Select
Do Until ActiveCell.Value = "stop"
Do Until IsEmpty(ActiveCell)
Selection.Offset(1, 0).Select
Loop
ActiveCell.Delete
Loop
View 9 Replies
View Related
Aug 10, 2009
I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.
I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.
Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.
View 9 Replies
View Related
Oct 19, 2009
I use some VBA that does many things and in the end it exports the files to a network drive and as a last step, it creates a Log, the log is basically one row for each file exported which includes the path and file, something like this:
Row 1: Headers...
Row 2: \networkpath odrivefolderDocument_file_name.xls
Row 3: \networkpath odrivefolderDocument_file_name2.xls
Row 4: \networkpath odrivefolderDocument_file_name3.xls
So, basically looking for a way to start at row 2 to end, and delete the files. Looking for a way to also maybe save/and archive them on my local Desktop on XP ?
View 9 Replies
View Related
Dec 14, 2009
Is there code to write that will delete all objects in a range of cells?
I have about 5 buttons at the top of my sheet and I don't want to erase them when I erase objects.
Say a range of D3:AB300
View 9 Replies
View Related
Jun 18, 2007
Find any code that deletes cell values in a range every 7 days.
cells.range("range").delete.currentdate + 7
every 7 days the cells in the range "range" get deleted etc
View 9 Replies
View Related
Oct 10, 2007
In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.
View 9 Replies
View Related
May 23, 2008
I am trying to select rows using constants I have set-up so that I can then delete those rows. However I don't know how to set-up the row formula to accept the constants. So far I have something like
Dim a As Integer
Dim b As Integer
row(a:b).select
selection.delete
View 3 Replies
View Related
Aug 1, 2013
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
View 9 Replies
View Related
Sep 18, 2009
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
The workaround:
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
View 10 Replies
View Related
Nov 28, 2013
Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.
View 3 Replies
View Related
Apr 9, 2014
I have a macro solved on another post that creates worksheets based on a Range and renames the new worksheet to the corresponding value. The range is edited on a regular basis, so to keep the file size down I need to remove any worksheets that no longer have a corresponding ID in the range.
The previous post is here
HTML Code:
Sub CreateTimeline()
Dim rng As Range
Dim rCell As Range
[Code].....
I'm sure that I could amend the above code (also removing the copy worksheet condition) to check the worksheets and delete any that no longer have no corresponding value in the range from the worksheet name.
View 4 Replies
View Related
Jun 23, 2014
I've got a code that generates some worksheets in a fairly large workbook. The code is run monthly and replaces the already existing worksheets.
The problem is that each newly generated worksheets contains more than 1 million rows, which means that the size of each worksheet is around 5MB.
I need a code which allows me to delete all blank rows starting with row 1000 in those worksheets.
View 14 Replies
View Related
Feb 15, 2014
I have the below given macro which does quite well at deleting the cell for a given value (Value = "_y-600_z-.jpg") in a given range. What I need for it to do is delete the cell contents if '_y-600_z-.jpg' is anywhere in the string. As an example, if the cell contains 'Handler_x-111251_y-600_z-.jpg' it will delete the cell contents.
[Code] ......
View 2 Replies
View Related
Feb 25, 2014
I have data that gets seperated into different cells and what i need is for all the data to line up from left to right with no empty cells in between.. so basically shift delete on each cell and shift the data left.
So i thought i know, ill simply f5, select the blanks, delete and shift left... however that option doesnt come up.. it simple says delete row?
What is the best way to select all the blanks in a range and delete them and have the cells that contain data move to the left so all data is next to each other.
View 1 Replies
View Related
Mar 1, 2014
Not sure if this is better suited for a macro or done via conditional formatting.
Trying to empty a standard template we use and of a certain range I need to empty all cells not containing N/A (typed not error).
View 2 Replies
View Related
Jun 17, 2009
I have a named range and I want to delete the rows which have zero value in the named range.
View 6 Replies
View Related