SpecialCells(xlCellTypeVisible)

Dec 24, 2009

I have the following VBA code which shades rows and works if the filtered rows are less than 11,000.


With ws.Range(ws.Range("A4"), ws.Cells(.Rows.Count, 1).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.OffSet(1).Resize(.Rows.Count - 1, ws.UsedRange.Columns.Count).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 4
.Parent.ShowAllData
End With

However if the number of filtered rows exceeds 11,000 I get an error (1004 - MS Excel cannot create or use the data range reference because it is too complex. ....). I understand that SpecialCells is limited to a certain number (8,192) of rows. How can I amend the above code to accommodate this?

View 9 Replies


ADVERTISEMENT

Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"

Apr 14, 2007

Having problems with trying to get my vba code to access the SpecialCells property. Receiving the following error.... Unable to get the SpecialCells property of the Range class. The section of my code is below that is causing the error. Keeps stopping on the "Selection.SpecialCells(xlsCellTypeVisible).Select" line.

Sheets(" Book Query").Range("A6:I6").Select
Sheets("Book Query").Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlsCellTypeVisible).Select
Selection.Copy
Sheets("Inventories and Variances").Select
Sheets("Inventories and Variances").Range("A7").Select

View 2 Replies View Related

SpecialCells - Selecting Cells With A Certain Value

Jun 11, 2008

How do I use SpeciaCells to select cells in a range with a certain value?

In the same manner that you can use SpecialCells to select blank cells and then delete that entire row I want to do the same for a cell containing a specific value/text

I have been using SpecialCells to assign a value in blank cells as a temporary marker. I then need to delete the rows with the temporary markers. I have not been able to use SpecialCells to select the temporary markers .

View 9 Replies View Related

Skip SpecialCells Action If There Are No Blank Cells

Aug 21, 2008

I am trying to run an action on a series of cells that inserts rows whenever there are blank cells (resulting from the formula) in column.

The problem: When I have a situation where there are not blank cells, the the .SpecialCells action does not work. I have tried the On Error Goto, but I have multiple equations that can have this occur and I have only been able to use that feature once in a macro.

LR = ActiveSheet.Range("B65536").End(xlUp).Row
Set Rng = Range("A2:A" & LR)
With Rng
.FormulaR1C1 = "=IF(OR(AND(RC[16]="""",R[-1]C[16]=1),AND(R[1]C[16]="""",RC[16]=1)),"""",1)"
End With
Set Rng = Range("A:A")
With Rng
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Insert 'stopped here
End With

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved