Macro To Delete Every Other Row Thats Filtered?

Oct 3, 2008

Can't quite figure out how to do this. I'm looking to create a macro that will delete every other row of a filtered set of data. for example, if these are the filtered rows:

row 21
row 22 delete
row 50
row 51 delete
row 58
row 59 delete.

... and so forth down to the bottom of the filter. I cant seem to record the macro using a relative reference with the filtered data.

View 3 Replies


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

Delete A Filtered Row

Feb 15, 2009

I use a filter to isolate one particular row of data. This unique row of data is data to be updated. There is ever only one record filtered, and that record is dynamic from the database of over 500 rows. Row 1 is the header. The filtered row is displayed under row 1 (physical row 2), but can be any of the row numbers according to the filter results. (ie the filter has extracted record (row) 13, and is displayed on physical row 2 below the headers)

How can I delete this filtered row. I can't simply

as this deletes record (data row) 2, not the 2nd physical row. I need to delete 2nd physical row regardless of the value determined by the filter.

View 9 Replies View Related

VBA To Delete Filtered Rows

Apr 14, 2012

I have recorded a macro which deletes the visible rows after an auto filter is run - ( auto filter is for custom and blanks).

I thought this was working, but when i look at the code, it looks as if the rows to delete have been hard coded in, and not adapting to just delete the filtered ones.

View 8 Replies View Related

Delete Filtered Rows - VBA?

Aug 19, 2013

So I have the following code:

'Remove UPC sales below $5000
ActiveCell.FormulaR1C1 = "=SUMIF(C[-3],RC[-3],C[-2])"


View 2 Replies View Related

Delete Rows From Filtered List?

May 11, 2012

I can see that I'm not allowed to delete rows from a list when it's filtered. Do I have any options to avoid this restriction?

View 2 Replies View Related

Delete Filtered Rows Returning?

May 6, 2014

What is required to delete the visible rows post filtering?

ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=9, Criteria1:= _
'insert code to delete visible rows
ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
ActiveSheet.AutoFilterMode = False
Selection.Delete Shift:=xlUp 'this is where the error is occuring.
End Sub

View 3 Replies View Related

How To Delete Visible Rows In A Filtered List

Jan 30, 2013

how to delete only visible rows in a filtered list?

View 9 Replies View Related

Delete Filtered Data, Leave Headers

Apr 30, 2009

I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.

How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)

View 4 Replies View Related

Large Tab File Delete Filtered Rows

Apr 1, 2009

I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.

I then highlight all the rows and try to delete them, but I receive the following error popup box:

"Microsoft Excel cannot create or use the data range reference because it is too complex."

How can I delete all these filtered = 0 rows without getting this error??

View 9 Replies View Related

Delete Visible Rows From Filtered Range As Table

Dec 11, 2012

I have a range that has been formatted as a table. Once I've applied a filter, if I try to select all of the rows, and delete the visible rows the option is greyed out.

Is there no way, short of vba, that I can just simply delete the visible rows?

View 1 Replies View Related

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 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

Macro That Goes Through A Filtered List

Sep 16, 2009

I'm trying to write a macro that goes through every value in a filtered list.

Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...

View 9 Replies View Related

Macro Fails When Data Is Filtered?

Mar 14, 2014

I have a macro that sends a sheet via email when the engineer has completed it and clicks a button. I added a filter to some of the results to remove blank lines and now the macro will not work. The problem is that if I remove the filter so that the macro does work, I end up with blank lines again.

View 4 Replies View Related

Macro To Copy Filtered Values

Oct 28, 2011

I have a large worksheet, with approx 15 columns of data, which is a straight data dump from a very old sales reporting system - so the whole thing is a mess.

As such, I auto filter the report, and select certain criteria from various columns, which leaves me with just the data I wish to see on screen.

What I then need to do is copy across any visible values (after this filtering) from Column C (missing out row 1, which is headings), into the same row in Column E.

At the moment, this is a manual process, because if you highlight the entire selection of codes in column C, then paste in Column E, due to the filtering, the paste puts the values in all the wrong places.

Is there a way around this - or a macro which will copy the values to the same row but in Column E?

View 4 Replies View Related

Deleting Filtered Fields Via Macro

Nov 6, 2012

I have an issue with trying to write a macro to apply a filter and then delete all lines.

Within the macro I copy and paste one sheet to a new one, and Paste Special Values over all formulas, leaving me with a column that has some figures and some "#N/A".



So I need to do something in the macro that then applies a filter on this column and deletes off all the #n/a results.

This table has a number of columns not just this one so it is the full rows that need removing.

I see on other posts that using a range.specialcells clause could work, but i'm not too sure exactly on how to apply this.

View 1 Replies View Related

Run A Macro That Only Runs On The Filtered Data

Jan 11, 2007

I have a spreadsheet that is made up of single line items with sub-totals at various points going down the sheet (ie the spreadsheet is split into sections)

Each section is about 100 rows long and 40 columns wide, 10 of which are sub-totals, all using the same formula from that i enter in column A.

As i add new sections i have recorded a simple macro that copies the active cell (ie A15 for example). It then pastes into D15, F15, H15, N15, X15 and then returns to A15.
I have linked this up to ctrl X for ease of running.

I have around 500 section totals to "ctrl X", per spreadsheet and 10 spreadsheets to do in total. I have firstly filtered all the line items (rows) out to leave me just the sub-totals to [ctrl x] [arrow down] [ctrl x] [arrow down] [ctrl x] etc etc etc....
Is there anyway to automate this process, i have tried recording another macro of me [ctrl x] [arrow down] about 30 times, but when i run it, it pastes on the hidden rows, not just on what you see on the screen. Is there a bit of code i can enter so it only sees what is visible.??

View 9 Replies View Related

VBA Macro To Copy Filtered Range

Apr 22, 2009

This code was provided this forum. It is so close to what I need. The only difference is that I need the copied lines inserted.

Actually, I needed Destination to pick EndT2 (on Sheet2), then select one row down from it and then insert what was copied... -R-

Sub CopyFilteredRangeNoHeaders()
Dim rTable As Range

Set rTable = Sheet1. AutoFilter.Range

Set rTable = rTable.Resize(rTable.Rows.Count - 1)
'Move new range down to start at the fisrt data row.
Set rTable = rTable.Offset(1)
rTable.Copy Destination:=Range("EndT2")

View 9 Replies View Related

Run Goalseek Macro On Filtered Data

Sep 26, 2007

I have written a macro that uses the goalseek function over an entire column. It works and is a happy macro. the only problem with it that I see is that it currently cannot work through a filtered list. Say, for instance, you have a column(a) that adds column(b) + column(c). Lets say that there's 10,000 rows and of those, 8,000 have a value over 10. You apply a filter and view the other 2,000 rows with values less than 10. If you run the macro in its current state you'd apply the results to all 10,000 lines - but I wish to do it only over the remaining 2,000.

I'm certain that some variation of .SpecialCells(xlCellTypeVisible) will be used but I cannot determine where. Here's my code.

Sub TryMe()
'set the goalseek area
answer = MsgBox("Please note that this macro only works on consecutive cells." & _
vbNewLine & "It will NOT work on a filtered list." & vbNewLine & vbNewLine & _
"Continue?", vbYesNo)
If answer = vbNo Then
Exit Sub
End If

With Application
On Error Resume Next
Application.DisplayAlerts = False ..............

View 5 Replies View Related

Copy Filtered Data To New Worksheet Using Excel Macro?

Aug 3, 2013

my requirement is as follows. I have a school data sheet(data sheet) and i have to filter data based on each school id.I need to pass school id in a different excel sheet(input sheet). So if the school id matches then i have to copy the entire data for tht school and move to to a new sheet.

Input sheet

Data Sheet

Emp No Surname last name schoolid
1 marian chin 211
3 luke mar 211
5 uyure tgt d 321
7 fdsfd fdsfsd 333

so whn the macro is run it should filter for 211 and 321 and move these three rows from data sheet to new output sheet.

View 1 Replies View Related

Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)

Jun 19, 2009

I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.


View 4 Replies View Related

Macro To Copy And Paste Auto Filtered Data To Existing Worksheet Below Previous Data

Oct 18, 2013

I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.

I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.

The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:

Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)

[Code] .....

View 6 Replies View Related

Macro To Delete Vba Code - Macro Security Warning

Feb 7, 2008

I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View 9 Replies View Related

How To Copy Filtered Data Into Filtered Data

Aug 19, 2013

Is it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.

View 1 Replies View Related

Macro To Delete Row

May 15, 2014

I have date from column a to k

I have a formula on column L

I want macro to delete all rows with #N/A

View 5 Replies View Related

Macro If Then Delete Row ()

Dec 20, 2006

Need a macro for the following situation, will be incorporated into my other macro.

For each row in column C,

if value for first 4 letters = "abc " or "def " or "ghi " or "jkl " then delete entire row.

View 9 Replies View Related

Delete Macro

Aug 30, 2007

I'm looking for code that will prompt a user to select column(s) to be deleted.

- With the exception of columns A,B,C.

- Only entire columns can be deleted

- Rows can not be selected for deletion

View 9 Replies View Related

MACRO: Delete Row Contains

Oct 23, 2008

I want to delete a row if it contains componenten.

In here i ask to delete componenten not.

Or should i take "*componenten*" or totaly someting else

For MY_ROWS = Range("c65536").End(xlUp).Row To 1 Step -1
If UCase(Range("c" & MY_ROWS).Value) = "COMPONENTEN" Then
End If

View 9 Replies View Related

Macro Delete Row

Jun 10, 2009

Have a sheet that has a $ value in Col J would like to run a macro that would delete the Row if the value is $0.00 and move the rows up.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved