Deleting Rows

[Randal]

I have two cell names: TopOfRange and BottomOfRange. Both are empty cells. In between these cells are some rows that I want to delete, but I need to keep the named cell there and available.

I am not good with offset command, but I can write this in English.

What I want to do is in sheet1 select TopOfRange, offset down one row, if that cell is BottomOfRange? Stop (there are no rows to delete). If that offset one row is not BottomOfRange, select those rows until BottomOfRange is found, then offset one row up, copy the selected rows in that range to another sheet (Sheet2). Go back to Sheet1 and goto that selected range that was copied and delete the rows.

-R-

 

[PCL]

Randal,
Can you check how is your VBA configuration
In VBA > Tools > References
what is checked:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
MicrosoftOffice 11.0 object Library

 

[Randal]

All sets are checked. Test on new attempt errored on:

LL = Application.WorksheetFunction.Right(MyADD, (Len(MyADD) - InStrRev(MyADD, "$")))/[code]
 
Error was: Runtime error '438': Object doesn't support this property or method. 
 
I am using Excel 2003, is this an issue?  I would hope that the solution would work in either 2003 or 2007.
 
Who would of thought just trying to view the bottom record in a listbox would be such an issue....   -R-

 

[PCL]

Randal,
I'm stuck, perhaps could you launch a new thread concerning this new topic:
Compile Error: Can't find project or library
Right and Left statement NOT recognized

 

[Randal]

Thanks for the help with this.... The actual problem has to do with and large listbox... when it is called up in a userform, the users cannot read the very bottom line because the scrollbar covers it. When I add a blank row to the bottom of the range it pushes up that unreadable row to be visible. It is of a lot to go through to get the result needed, but the user needs to see that last row of data. There are other implications of having that blank row in there as records are added and taken away from the listbox.

Thanks for trying.. -R-

 

[PCL]

Randal,
I see your need, the problem looks like an Excel configuration issue as far as statements (LEFT , RIGHT) lead to a compilation error.
PCL