deleting wrong range!
 

HI all,

I am at ropes end and am hoping someone can please look at my smal
workbook and please tell me WHY......my code iss deleting part of m
terms and conditions as well as my selected range.

Please, i am out of ideas!!!!

CHeers!!!

--
Message posted from http://www.ExcelForum.com

deleting wrong range!
 

So where's the code ?


"gavmer " wrote in message
...
HI all,

I am at ropes end and am hoping someone can please look at my small
workbook and please tell me WHY......my code iss deleting part of my
terms and conditions as well as my selected range.

Please, i am out of ideas!!!!

CHeers!!!!


---
Message posted from http://www.ExcelForum.com/

deleting wrong range!
 

Sorry all, here is the code or i can e-mail a workbook??

Private Sub CommandButton1_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("foldunit2")
x = rng.Rows.Count
rng.Range("foldunit2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub

Private Sub CommandButton2_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("knifeunit2")
x = rng.Rows.Count
rng.Range("knifeunit2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub
Private Sub CommandButton3_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("knifeunit3")
x = rng.Rows.Count
rng.Range("knifeunit3").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Su

--
Message posted from http://www.ExcelForum.com

deleting wrong range!
 

This looks funny to me:

Set rng = wsquote.Range("foldunit2")
x = rng.Rows.Count
rng.Range("foldunit2").ClearContents

maybe
wsquote.range("foldunit2").clearcontents
or just
rng.clearcontents

is what you want.

=====
Try this on a new workbook/worksheet:

Option Explicit
Sub testme01()

Dim wsQuote As Worksheet
Dim rng As Range

Set wsQuote = Worksheets("sheet2")

With wsQuote
.Range("b1").Name = "foldunit2"
Set rng = .Range("foldunit2")
End With
MsgBox rng.Address & vbLf & rng.Range("Foldunit2").Address

End Sub

In my example, FoldUnit2 refers to B1. But when I use:
rng.range("foldunit2"), it's like writing:
rng.range("b1")
which means start in B1 and go down one row and to the right one column.

msgbox range("a1").range("b1").range("c1").range("d1").ad dress

will work, but may not be what you really mean!

======
if that didn't work for you, then I'd try stepping through the code with a minor
change:

Change this line:
rng.Offset(0, 0).Resize(x).EntireRow.Delete
to this pair of lines

rng.parent.select
rng.offset(0,0).resize(x).entirerow.select

======
and rng.offset(0,0) is the same as rng.
and rng.resize(rng.rows.count)
is the same as rng
(when x = rng.rows.count)

why not:
rng.entirerow.delete
?




"gavmer <" wrote:

Sorry all, here is the code or i can e-mail a workbook??

Private Sub CommandButton1_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("foldunit2")
x = rng.Rows.Count
rng.Range("foldunit2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub

Private Sub CommandButton2_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("knifeunit2")
x = rng.Rows.Count
rng.Range("knifeunit2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub
Private Sub CommandButton3_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet2")
Set rng = wsquote.Range("knifeunit3")
x = rng.Rows.Count
rng.Range("knifeunit3").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson