deleting wrong range!

G

gavmer

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!!!
 
G

gavmer

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
 
D

Dave Peterson

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").address

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
?
 
Top