Selecting displayed cells only

[collierd]

Hi

Am I using the right method to select visable rows only?

After filtering, to select only the cells shown I use:

Selection.SpecialCells(xlCellTypeVisible).Select

On its own, this works

I have a script that performs this action multiple times (via a do while loop)

The first time it works, the second time it only selects the header
I've stepped through it and, before executing the above command, it displays all the rows I require
It just doesn't seem to select them

Any thoughts?

Damian.

 

[SkipVought]

Hi,

You need to post your code.

Chnaces are that you never reassign the Selection to another range.

Skip,

_[red][/red]

 

[collierd]

ok, Skip

Top10_Bottom10() calls it
Where would I perform the reassignment?

Thanks for the feedback

Damian.

Function CreateWorksheet(branch As Integer)

' Top_10_Bottom_10 Macro

    Dim newSheet As Worksheet
    Dim FirstCol As Integer
    Dim FirstRow As Integer
    Dim LastCol As Integer
    Dim LastRow As Long
    Dim Top10Row As Integer
    Dim workSheetName As String
    
    FirstRow = 2
    FirstCol = 1
    Top10Row = 12
    
    Selection.AutoFilter Field:=1, Criteria1:=branch
    
     ' Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row


    If LastRow <> 1 Then

    ' Find the LAST real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
    
    ' Select visable cells
    Selection.SpecialCells(xlCellTypeVisible).Select
    
    Selection.Copy
    Set newSheet = Worksheets.Add
    'Worksheets.Select
    'Worksheets.
    Selection.PasteSpecial Paste:=xlPasteValues,      Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    'Find the branch number
    newSheet.Name = "Branch" & Cells(2, 1)
    
             ' Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    ' Find the LAST real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
    
    ' Select everything bar top 10 and bottom 10 rows
    ActiveSheet.Range(Cells(Top10Row, FirstCol), Cells(LastRow - 10, LastCol)).Select
    
    ' Delete rows
    Selection.Delete Shift:=xlUp
    
    End If
    
End Function

Sub Top10_Bottom10()

    Dim counter As Integer
    Dim endcount As Integer
    
    counter = 22
    endcount = 27
    
    Do While counter <> endcount
      Sheets("Sheet1").Select
      ActiveSheet.ShowAllData
      Call CreateWorksheet(counter)
      counter = counter + 1
    Loop
    
End Sub

 

[SkipVought]

Starting in your MAIN, where do you SELECT a range?

    Selection.AutoFilter Field:=1, Criteria1:=branch
[/b]
this selection uses whatever happens to be selected when the sheet is activated.

second time thru the selection is LOST.

Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[collierd]

Doesn't

Sheets("Sheet1").Select
ActiveSheet.ShowAllData

do that as this should revert me to the original worksheet with its original contents

I then call it with the branch number

 

[SkipVought]

Well need to reference a specific range in your code with Selection THATS the problem!

I personally avoid using the Select method.

I recommend using range references.

Skip,

_[red][/red]

 

[collierd]

ok, i'll give that a go

Thanks Skip




damian.

 

[firefytr]

Also, again, plase do not use ActiveSheet. You're asking for trouble. Assign your ranges and your sheets to variables and reference them that way. You'll be much better off.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci