Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete last row from range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete last row from range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete last row from range
I believe that if these is nothing in the range, the rows.count part won't
work. Think about it. -- HTH, Barb Reinhardt " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete range using VBA | Excel Discussion (Misc queries) | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
Range.Delete and Range.Resize.Name performance issues | Excel Programming | |||
Delete range | Excel Programming |