Deleting Entire Rows Once Filter Has Been Applied

Nov 24, 2011

I've got a report which is approx 40,000 rows long and I need to delete out alot of the info.

I need to delete out entire rows if column C contains a 0.

I've written a loop to do this but as theres so much data it take a long time to run.

Is there a smarter way to do this?

View 6 Replies


Deleting Entire Rows Containing A Specific Value

Aug 29, 2007

I've been tasked with developing a macro that will generate schedules. For background, we're taking a QuickBooks Customer file and exporting it to be picked up by the macro. I've got that part working, but now I need to delete various rows based on certain criteria.

The spreadsheet has a field for Job Status which we use to define whether an account is under contract (2 in QuickBooks), or some other status. Basically what I need to do is delete the entire row of data where JobStatus (Column R) is anything but 2.

View 9 Replies View Related

Deleting Duplicate Cells (not Entire Rows)

Dec 17, 2007

I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.

Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.

I also cannot install any add-ons as this on a work computer.

View 9 Replies View Related

Deleting Rows Based On Multiple Filter Options?

Aug 3, 2012

I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.

View 9 Replies View Related

Adding A New Row To A List When Filter Is Applied

May 14, 2009

If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.

View 2 Replies View Related

Sum Filtered Data After Filter Is Applied

Jan 20, 2009

I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?

View 9 Replies View Related

Macro To Filter And Delete Row Not Deleting Visible Filtered Rows

Jun 6, 2013

I'm working through a filter macro to delete unecessary rows of data from my dataset.

- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"

The macro filters the range fine, but when if comes to deleting the row, the macro stops.

Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then

With Range("CanadaData")

[Code] ......

View 2 Replies View Related

VBA To Copy Specific Columns After Filter Applied

Feb 4, 2014

Sheet one contains a table of table from C6:AY7000

On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.

On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.

So BN3 = Sheet2C3
And BN4= Sheet2C4

I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.

This is the coding I have written to select and filter the correct data values.

Sub AddFilter()
' AddFilter Macro
Dim rCrit1 As Range, rCrit2 As Range
With Application
.EnableEvents = False
.ScreenUpdating = False

[Code] .......

This now gives me the correct data in the table.

I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY

The copied data will then be pasted into sheet 2 starting in cells B9

I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.

View 2 Replies View Related

Deleting Duplicate Row (8 Cells) And Shifting Unique Rows Up - 3 Columns As Filter

Feb 4, 2014

I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).

As filter I would like to be used A,B,C columns.

Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1

Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

View 1 Replies View Related

Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

View 5 Replies View Related

Deleting The Entire Row In VBA

Dec 24, 2007

I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code,


Sub Delete_Zero_Factors()

Dim i As Integer
Dim CUSIP As String


i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
CUSIP = ActiveCell.Offset(i, 0).Value

Do While (ActiveCell.Offset(i, 1).Value

View 9 Replies View Related

Deleting An Entire Column

May 2, 2006

Need the code for deleting multiple columns simultaneously in an excel depending on some search criteria.Suppose I want to delete the columns whose names are "Roll No" ,"Age" & "Class"

View 2 Replies View Related

Deleting Entire Row That Has Cells With Certain Strings?

Mar 16, 2013

I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.

Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub

View 3 Replies View Related

Deleting Entire Column - Statement Ignored

Apr 11, 2012

Working on some code that will find the Column Name "CL" and delete the entire column. Here is what I have so does not give me a error when it compiles, but for some reason the statement in red is being ignored. I tried switching Range with Columns, but that did not work.

Private sub coldelete ()
Dim Rng2 as Range

shUCLR = sheets("Unit Cost").usedrange.rows.count
Set Rng2 = Sheets("Unit Cost").Range("A1:BF1")

For Each d In Rng2
If d = "CL" Then
colnum = d.Column
colletter = Left(Sheets("Unit Cost").Cells(1, colnum).Address(False, False), (colnum)

View 2 Replies View Related

VBA Deleting Entire Row When Certain Word Appears?

Dec 12, 2012

I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.

Sub test()
Dim r As Range
With ActiveSheet[code]....

The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.

How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?

View 3 Replies View Related

Macro Is Deleting My Entire Sheet

Nov 15, 2006

Sub Delete()
Dim x As Long
For x = 28 To 8 Step -1
If Range("b" & x) = "" Then _
Range("b" & x).EntireRow.Delete
Next x
End Sub

View 9 Replies View Related

Deleting Entire Customer Entry If One Cell Is Blank?

Dec 24, 2008

I have a large database of customers. I would like to edit this database so that it only lists those customers that have an email address listed in that particular column. I can then save that as a separate list to upload to my marketing campaign.

Is there an easier way than just deleting entire rows at a time? About a 2,000 name list with maybe 30% having email addresses.

View 2 Replies View Related

Deleting Entire Columns Based On Cell Formatting

May 15, 2009

I'm trying to come up with a macro that can delete an entire column based on formatting of the cells. Basically what I have is a range of cells, and if all cells in a particular column have no fill color, then delete the entire column.

View 9 Replies View Related

Filter Out Duplicates Other Than Sorting And Deleting Manually

Mar 13, 2009

I have a report created daily with account numbers, however there are duplicates. Is there a faster way to filter out duplicates other than sorting and deleting manually?

View 4 Replies View Related

Error Clearing Filter After Deleting Records

Feb 3, 2007

I have a macro (below) that takes a CSV File and creates multiple worksheets and then filters specific records out of each newly created sheet. The creation of the sheets works fine. But, after setting the filter and then deleting the selected records, when I try to 'Show All', which should leave the unfiltered records, I get an error indicating something is wrong with the 'ShowAllData' method.

Here is the macro up to the point where the error occurs: ...

View 6 Replies View Related

Find Duplicates In 1 Col Then Copy Entire Rows Of These Duplicate Rows?

Feb 2, 2014

The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.

The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:

Original Sheet:
"A" "B" "C" "D"


View 7 Replies View Related

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro

Jun 9, 2009

I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).

Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.

Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to

View 9 Replies View Related

Code For Deleting Rows Verses Hiding Rows

May 25, 2013

The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.

For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell

For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell

View 4 Replies View Related

Delete Blank Rows (formula Not Deleting All Rows)

Sep 30, 2008

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
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.

View 9 Replies View Related

Deleting Rows Macro :: Rows That Don't Contain Word ...

Jan 9, 2008

I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"

My code if needed is:

Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
End If
End Sub

View 9 Replies View Related

Delete Entire Rows If Rows Below Has Value

Jun 28, 2007

I have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.

Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.

I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work

View 6 Replies View Related

Error In Filtering Table - Additional Rows Shown / Rows Hidden When Filter Removed

Mar 4, 2014

I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.

When I start using filters, I run into problems:

Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.

Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)

Step 3: Clear the filter of Unit

Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!

Attachment 301726

View 4 Replies View Related

Deleting Rows/content Of Rows

Dec 11, 2009

Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).

View 2 Replies View Related

Keep Entire Rows Of Spreadsheet Together

Jan 4, 2013

I've got spreadsheet I use to control the inventory of my user equipment; who has it, what is it, where is it, etc.

I'd like to "lock" multiple consecutive cells of each row so that they never seperate. This is because they need to be together. However, periodically, I need to move, sort, or rearrange the sheet.

Is there a way I can "idiot proof" this so that these cells never come unlocked from each other?

There is no vba involved (primarily because I don't know how), and no other scripting either.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved