Any way that the following code could perform faster? Unfortunately, i'm deleting 35,000 rows of data quite frequently from a filtered list. Could be up to 35 columns wide.
Code:
ls = Cells(Rows.Count, 1).End(xlUp).Row
If ls > 1 Then
Range("A2:A" & ls).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.
The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.
This is the main routine where I progress down the big lookup table.
'Work down the Price File range from top to bottom
Set RefTableRange = DSWPrices.Range("DSWPriceRange")
RefTableIndex = 1
Application.Calculation = xlCalculationManual
While RefTableIndex < RefTableRange.Rows.Count
RefTableIndex = RefTableIndex + 1
'check if this part number is included in the contract
Call CheckRefTableRow(RefTableRange, RefTableIndex)
Wend.....................................
Every time i do a filter and copy a data. Keep the filter on and past in another column. The data does not copy just into the visible cells. It pretend the filter is not there.
I have tried selecting only vible cells when copying and pasting into cells by selecting only visible cells but does not seem to work. Get the error message "command cannot be used on multiple section". There must be a solution but looked on the web and really cannot find one.
Table below so i filter out the "a" so i just have numbers then want to past into vible cells.
CopyPaste ResultsResults wanted
111
a2
232
a4
33
a
44
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.
I can't understand how Delete works when applied to a table.
Here is my table:
Number
Letter
Test
[Code]....
If I use "EntireRow.Delete", I have an error.
If I use "Delete" only, it works for A and B, but for C, it suppresses the Letter column.
By the way, I don't understand why I have to include header (Table1[[#Headers],[#Data],[Letter]]) when I filter to find unique letters at the beginning of the script.
how to delete only visible rows in a filtered list?
View 9 Replies View RelatedI'm trying to apply an autofilter to do the ol' "delete visible lines that meet criteria" trick. Unfortunately my vb macro, although applying the string correctly, hides all of the lines...
This is the part of code relevant to filtering (i'm trying to filter on column M which is 13):
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'm trying to clean up several long worksheets by removing rows that don't contain a visable number or letter.
I want to remove all rows that only contain either empty cells, formulas that evaluate to a empty text string (""), cells that contain error values, or cells that contain zeros, or any combination of the previous.
I've stumbled onto the following, but it does not work.
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 am asking if it is possible to delete filtered rows? With code.
What I mean is after setting a filter, then deleting all shown rows except row 1, (Titles)
I did a search but nobody seams to have asked this yet, so recorded it, but that did not seam to enlighten me much either.
Or is this the wrong approach, should I delete using a loop, using the filtered criteria as to say delete row, or move on to the next row?, this would be far more time consuming as when all can be deleted at once if possible of corse.
I am using code to filter my 4 sheets Greater then 0 (zero)
After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:
for 1st sheet with the name ("V2")
for 2nd sheet with the name("LV")
For 3rd sheet with the name ("F2")
and 4th sheet with the name("L2")
If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well
But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.
So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.
View 3 Replies View RelatedI have a table of data with a filter on the top and a sum total at the bottom. Whenever i filter the list the total at the bottom doesnt change. how do i change it so the sum only calculates the visible cells?
View 3 Replies View RelatedI have a large spreadsheet that I am using multiple drop downs in to sort for
different scenarios. I do not want to use the general data subtotal command
but each time I sort I want to get a total for the visible cells -
I have finished a huge project. The final step is sending emails.
I have a filtered range. The email .HTMLBody is formed using the visiblecells, and forms an object called 'rng'
Only problem is - now I want to add my attachments. Within the visible range, the URL of the attachment is available (column 5).
So need a loop to do for each cel in rng.Column(5)
.Attachments.Add rng.Cells(cel.row?,5)
Can't get the row number of the visible cell to work. Once this is solved I can click the button and let the beast send out 1000+ emails. But at the moment cannot Was also thinking instead of For each cel, could use i = 1 to (last row in visible rng) ?
I have a need to sum only the visible cells in a row. Certain columns are collapsed depending on the month and there is a YTD colum that I only want to pick up the expanded or visible cells. I found a custom function listed below at Microsoft's website but it only allows for a single range to be entered and I need to pick 12 individual cells, for example A1, C1, E1, etc. There is other information in the cells between and that is why the range won't work. Is there a better way to do this or can this function be modified to allow me to do this?
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
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 RelatedI have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies View RelatedI have a data in which I have inserted a table with headers.
I then filter the type of data I want removed from the table using the drop down
Then goto special, visible cells only, press delete.
Now, the data is gone, but when I select all again to pull up the entire table, the data is gone, but rows are still there. So now I have tons of empty rows in between the rows of needed data. Any way of removing the rows in between as well as the data?
I have a spreadsheet that has many rows of data. I've filtered the spreadsheet, and want to paste a formula so that it appears only in the filtered rows. How can i do that?
View 1 Replies View RelatedI've setup a worksheet whereby rows can be Autofiltered between a start and finish date via a Userform which (after much mucking about with UK/US dates) seems to be working nicely now. With the results of the Autofilter, i wish to count certain entries (in this case entries that are in column F) in the remaining visible rows.
The Workbook is about 'Cases' worked, and column F relates to how the 'Case' is cleared (Call, Letter, Referral). Effectively I want to be able to count 'Call', 'Letter' and 'Referral' entries once the Autofilter has been applied.
I had a situation in which i need to number the cells which start with the Text "SB" in column B. To elaborate,i want to give a serial number for all the transactions starting with "SB". For this i filtered the data >> Custom>>Begins with>>"SB"(Quotes Ommitted). I want to increment the cells with the serial numbers 1,2,3 and so on only those which contain SB. I tried using it with the ROW Function.
View 5 Replies View RelatedI am looking for a COUNTIFS that is only looking only at the visible cells. I have found a few threads on this subject but none for this specific problem.I know there is a SUMPRODUCT / SUBTOTAL excel solution for COUNTIFSing visible cells only. How modify it to take multiple ranges and criteria like CONUTIFS does.
[Code].....
I've got an AUTOFILTER and would like to add only the visible cells in a particular column (column E). As the user changes the filter, the total would change - but I'm not sure where to even start with this one. I've attached a sample file.
View 2 Replies View RelatedI have a worksheet with 26 columns of financial data. ACTUAL and BUDGET for each month and year totals for ACTUAL and BUDGET. I've provided the user with the ability to hide months they don't need to see during their current session. For example, if they are working on May, they may choose to hide June through December.
A user has requested Year To Date totals for ACTUAL and BUDGET. I thought this would be relatively simple. Just add two more columns and add up the visible cells.
I got this function from the Microsoft website and it works if I don't add criteria:
I have a spreadsheet with a lot of hidden rows in.
How can I do a count that only counts information in the visible cells?
How do I modify this formula to calculate visible cells?
=AVERAGE(IF($U$8:$U$720="AM",$H$8:$H$720))
I've got a macro with copies certain data from 1 sheet to the next. The problem I have is that some times there is an autofillter on the data.
I think I need to add .SpecialCells(xlCellTypeVisible) in to the code some where but im not sure where.
my current code is as follows. This is only a part of the code. It also copies other columns across to the other sheet
Code:
Sub CopyPasteMacro()
'
With Sheets("Imprint Data")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rngToCopy = .Range(.Range("B6:B" & LastRow), .Range("B6:B" & LastRow))
End With
rngToCopy.Copy
Sheets("To Key @ TE Imprint").Range("D4").PasteSpecial Paste:=xlPasteValues
End Sub