Delete last row from range
On Sep 7, 7:56 pm, Barb Reinhardt
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
" 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
|