I believe that if these is nothing in the range, the rows.count part won't
work. Think about it.
--
HTH,
Barb Reinhardt
"(E-Mail Removed)" wrote:
> On Sep 7, 7:56 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
> > Here's some test code you could try
> >
> > Dim Rng As Range
> > Dim myRange As Range
> > Set Rng = Range("A2:Z200")
> > Debug.Print Rng.Row, Rng.Rows.Count
> > Set myRange = Cells(Rng.Row, Rng.Column).Offset(Rng.Rows.Count - Rng.Row +
> > 1, 0)
> > myRange.EntireRow.Delete
> >
> > --
> > HTH,
> > Barb Reinhardt
> >
> >
> >
> > "condo...@shaw.ca" wrote:
> > > Hi,
> >
> > > I have code as follows,
> >
> > > Dim DataRow As Long
> > > Application.ScreenUpdating = False
> > > DataRow = ProjectsListBox.ListIndex + 1
> > > With ThisWorkbook.Worksheets("Data")
> > > Rng.Rows(DataRow).Delete xlShiftUp
> > > ' If listbox entries have all been deleted, reset the 'Rng'
> > > object _
> > > ' so that a valid rowsource can be updated.
> > > If Rng Is Nothing Then
> > > Set Rng = .Range("S2").Resize(, 2)
> > > Else
> > > Set Rng = .Range("S2").Resize(Rng.Rows.Count, 2)
> > > End If
> > > ProjectsListBox.RowSource = Rng.Address(External:=True)
> > > ProjectsListBox.ListIndex = -1
> > > End With
> >
> > > My problem is with my set 'Rng'. If the last row of the range object
> > > 'Rng'
> > > is deleted, "Rng Is Nothing" always returns false and then errors. Rng
> > > is
> > > initially set to a worksheet range and works well until the last entry
> > > is deleted.
> > > Any suggestions or alternatives?
> >
> > > Regards,
> >
> > > Stefano- Hide quoted text -
> >
>
> Thanks for the test code Barb, however, my test at "If Rng Is Nothing
> Then" still
> puzzles me. It always returns false possibly letting me know that I am
> using a poor
> construct. I guess I could use "If Rng.Rows.Count = 1 Then" .....
>
> Regards,
>
> Stefano
>
>
|
Similar Threads
