Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRange).

[AndyD]

hi am using the following to tag all visible (ie selected cells) with a "X" and excel is marking the cells right down to end...any advice of what i can check appreciated
thanks
Andy

Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)) = "X"

More info
1) There are filters on each cell in row
2) column A is hidden
3) headings are in row 17
4) Each record in Column B starts off being tagged as "Y" using range in column C (this bit works ok)

 

[Richie(UK)]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

Hi Andy,

Visible cells and selected cells are not the same thing.

Are you trying to apply a value of "X" to each cell in a selected range (using mouse/keys) or are you trying to apply the value to the visible cells after an AutoFilter has been applied? (or something else?)

 

[AndyD]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

hi Richie
Thanks for mail....Im trying to apply X for cells selected through data-filter-autofilter
Thanks
Andy

 

[Richie(UK)]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

Hi Andy,

I've been lazy and not bothered with code for the filtering of the range itself, or checking if the filter is applied. The following assumes you have already applied the AutoFilter.

Sub AFX()
    Dim rng As Range
    
    With Sheet1.AutoFilter.Range
        Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        'move and resize the range to exclude the header
        rng.Value = "X"
        'set the desired value
    End With
    
End Sub

HH

 

[AndyD]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

Hi thanks for your reply
Sorry hww do i move/resize the range..?
my headings are in 2 rows (17 and 18), my "range" is in Column C
Thanks
Andy

 

[AndyD]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

plus i get run time error: 91....."object variable or with block variable not set"

 

[Richie(UK)]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

Hi Andy,

The comment line in the code is telling you what the code does - it moves and resizes the autofilter range. You don't need to do anything.

plus i get run time error: 91....."object variable or with block variable not set"

As I said, I haven't included any checking to if the range has been AutoFiltered - has it been applied before you try incorporating the above code (this is the most likely cause of the error)?

HTH

 

[Juan Pablo González]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

plus i get run time error: 91....."object variable or with block variable not set"

Do you have the AutoFilter turned on ?

 

[AndyD]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

hi...yes is turned on...
I actually need this to work with/without records being filtered so i can tag all records or just those visble/selected ones?
Any ideas for me?
many thanks
Andy

 

[tusharm]

Re: Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRan

hi am using the following to tag all visible (ie selected cells) with a "X" and excel is marking the cells right down to end...any advice of what i can check appreciated
thanks
Andy

Seems to work OK wih and without any active filter.

?Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRange)).SpecialCells(xlCellTypeVisible).Address 
$B$18:$B$24

as well as

?Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRange)).SpecialCells(xlCellTypeVisible).Address 
$B$24
$B$18:$B$24

Intersect([B18:B65536], Range([B18], ActiveSheet.UsedRange)).SpecialCells(xlCellTypeVisible).value="x"

You may want to check your UsedRange.