Range Problem

[nemesischild]

I am trying to select a range of cells between to specific cells based on there style

I can pick as tart point and an end point easy enough
but when I try to define the area between the two as a range then I am getting the following error/

"Application-Defined or object-defined error"

I have 2 problems really
1. Is getting the Range to set
2. I may have to set multiple ranges and don't know how many I may need.

Dim iTabcount As Integer
Dim n As Integer
Dim aIndex As Integer
Dim iR As Integer
Dim iC As Integer
Dim sCurrent As String
Dim oSheet As Worksheet
Dim iROw As Integer
Dim startcell As Integer
Dim endcell As Integer
Dim nRange As Range
  

iR = 1
iC = 1

'Set oWorkbook = ActiveWorkbook
iTabcount = Application.ActiveWorkbook.Worksheets.Count
aIndex = 0
'Range
For n = 1 To iTabcount
 
   Do While Count <= 5
 
  ' Find a secton which contains n(currently 5) amount of blank cells to define the last populated cell in the column
  If Worksheets(n).Cells(iC, 1).value = "" Then
   Count = Count + 1
  Else
   Count = 0
  End If
   iC = iC + 1
  Loop
   Count = 0
 
  Set oSheet = Application.ActiveWorkbook.Worksheets.Item(n)
  
  iR = iC - 5

  
  iROw = 1
  startcell = 1
  endcell = 1
  
  'For Each Cells In oSheet
  If oSheet.Name <> "Suite_Data" Then
   'If oSheet.Cells.Hidden = False Then
    Do While iROw <> iR
     'Check the style of the cell
     If oSheet.Cells(iROw, 1).style = "TEST Case Ref" Then
      ' adds the value of that cell top a list box
      'LstBx_includedTests.AddItem (oSheet.Cells(iROw, 1).value)
      ' Set the start of the next search start point
      startcell = iROw
      ' start searching from the next cell after the current one which had the defined style
      endcell = iROw + 1
      'Start looping through the cells again
      Do While startcell <> iR
         ' check for the style again
         If oSheet.Cells(endcell, 1).style = "TEST Case Ref" Then
        ' Set the end cell back one
        endcell = endcell - 1
       'set the range to be all the cells between the styles
       nRange = Sheets(oSheet.Name).Range(Cells(startcell, 1), Cells(endcell, 500))
        'hide the specified range
        nRange.Hide
       End If ' End the stykle search to crweate range within
        'inctrement the next cell to search for creating the range
        endcell = endcell + 1
      Loop ' end the range serch loop
     End If ' end the find start pioints check
     'increment the row
     iROw = iROw + 1
    Loop 'end the main create ranges loop
   End If


Next
End Sub

 

[SkipVought]

Hi,

Just drinkin my java and purusing your code and I see that you do not have a true concept of the Excel Object Model--particularly, worksheets and ranges.

Symptoms:
Set oSheet = Application.ActiveWorkbook.Worksheets.Item

' Find a secton which contains n(currently 5) amount of blank cells to define the last populated cell in the column
If Worksheets.Cells(iC, 1).value = "" Then

Please read up on the Excel Object Model

Please state, simply, concisely and completely, exactly what you are tyring to accomplish WITHOUT reference to code.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[nemesischild]

Ok

what I am trying to do is cycle through a woorkbook looking for cells set to a specific style in this case "TestRef" in all viewable (i.e not hidden) worksheets
The text within the cells found to have the specified style is then listed in a list box

<This I have done>


Next thing I want to do is to be able to select one of these listbox entries and hide it from view within the workgroup it is contained in

the problem being that its not just the cell containing the style I wish to hide but all the rows from it to the row before the next cell found containing the same style.

Assumed that a range was the best solution to this.

my lack of undertanding is due to having only briefly worked with VB a few years ago and that I am in fact a tester trying to solve a test problem rather than a programmer trying to code an app around Excel.

Any help would be appreciated

Cheers

 

[SkipVought]

The range to set can be based on the start row and end row as follows...

With MySheet
  .Range(.Cells(StartRow, 1), .Cells(EndRow, 1))
End with

to hide...

With MySheet
  .Range(.Cells(StartRow, 1), .Cells(EndRow, 1)).EntireRow.Hidden = True
End with

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}