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.
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] ......
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
rows("2:2").Select
Selection.Delete
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.
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.
So I have the following code:
'Remove UPC sales below $5000
Range("G38").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-3],RC[-3],C[-2])"
[Code]....
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 RelatedWhat is required to delete the visible rows post filtering?
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=9, Criteria1:= _
"Other"
'insert code to delete visible rows
ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Select
ActiveSheet.AutoFilterMode = False
Selection.Delete Shift:=xlUp 'this is where the error is occuring.
End Sub
how to delete only visible rows in a filtered list?
View 9 Replies View RelatedI 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.)
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??
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?
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 RelatedHow do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI'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...
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 RelatedI 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?
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".
i.e.
13.75
12.00
#N/A
14.00
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.
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.??
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")
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
restart:
With Application
On Error Resume Next
Application.DisplayAlerts = False ..............
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
schoolid
211
321
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.
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.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
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] .....
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.
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 RelatedI have date from column a to k
I have a formula on column L
I want macro to delete all rows with #N/A
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.
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
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
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
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