Solved: Delete or ClearContents

[saban]

Set d = Columns("H").Find(What:=s, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
d.Offset(0, -7).Delete

Excell stops responding at line
d.Offset(0, -7).Delete

I want to delete 7 cells to the left of value d

Any ideas
Thnx

[ALe]

it works with me. are you sure d is not Nothing?

[ALe]

If you search s as character
Set d = Columns("H").Find(What:="s", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
MsgBox d.Address
d.Offset(0, -7).Delete

If you search s as string
Set d = Columns("H").Find(What:=s, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
MsgBox d.Address
d.Offset(0, -7).Delete

Both cases the value must be found in order to avoid errors

[saban]

no d is the value of s which is value from column K

Strange you say it works at you ???

s is range ??

[ALe]

I don't understand. Please tell what d, s are (string, cells) and what you need.

[saban]

Private Sub CommandButton2_Click()
Dim s As Integer
s = Range("T5") + 1
Dim TargetCl As Range
With ActiveSheet
Set TargetCl = Range("a65536").End(xlUp).Offset(1, 0)
With TargetCl
.Value = Me.txtNombre
.Offset(0, 1).Value = ActiveSheet.Name
.Offset(0, 2).Value = Me.txtEntidad.Value * 1
.Offset(0, 3).Value = Me.TextBox1.Value
.Offset(0, 4).Value = Me.txtOficina.Value
.Offset(0, 5).Value = Me.txtControl.Value
.Offset(0, 6).Value = Me.txtCuenta.Value
.Offset(0, 7).Value = s
End With
End With
Set TargetCl = Worksheets("Izpisi").Range("a65536").End(xlUp).Offset(1, 0)
With TargetCl
.Value = Me.txtNombre
.Offset(0, 1).Value = ActiveSheet.Name
.Offset(0, 2).Value = Me.txtEntidad.Value * 1
'.Offset(0, 3).Value = Me.TextBox1.Value
.Offset(0, 4).Value = Me.txtOficina.Value
.Offset(0, 5).Value = Me.txtControl.Value
.Offset(0, 6).Value = Me.txtCuenta.Value
.Offset(0, 7).Value = s
End With
Call Sort2
Dim ThsBook As Workbook, c As Range, Header As Range, del As Range
Dim lMax
Dim odgovor
Set Header = [K2]
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then 'And Not c.Address = Header.Address Then
MsgBox "Ta vnos je probremenil skupino Št vnosa je : " & s
odgovor = MsgBox("Ali želiš izbrisati ta vnos", vbYesNo)
If odgovor = vbNo Then
Unload Me
Exit Sub
End If
Unload Me
lMax = WorksheetFunction.Max(Columns("H:H"))
Set del = Columns("H").Find(What:=lMax, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
del.Offset(0, -7).Value.Delete
Exit Sub
End If


ActiveWorkbook.Save
Unload Me
End Sub

here is the whole sub
I have amended it a little bit but excel still freezes-stops responding when it comes to

del.Offset(0, -7).Delete

I need to find value zaseden in Column K which it does then if found find the max value in column H and then delete this value in column H and 7 cells to the left

[ALe]

Try this and tell me if you get error with the msgbox

Private Sub CommandButton2_Click()
Dim s As Integer
s = Range("T5") + 1
Dim TargetCl As Range
With ActiveSheet
Set TargetCl = Range("a65536").End(xlUp).Offset(1, 0)
With TargetCl
.Value = Me.txtNombre
.Offset(0, 1).Value = ActiveSheet.Name
.Offset(0, 2).Value = Me.txtEntidad.Value * 1
.Offset(0, 3).Value = Me.TextBox1.Value
.Offset(0, 4).Value = Me.txtOficina.Value
.Offset(0, 5).Value = Me.txtControl.Value
.Offset(0, 6).Value = Me.txtCuenta.Value
.Offset(0, 7).Value = s
End With
End With
Set TargetCl = Worksheets("Izpisi").Range("a65536").End(xlUp).Offset(1, 0)
With TargetCl
.Value = Me.txtNombre
.Offset(0, 1).Value = ActiveSheet.Name
.Offset(0, 2).Value = Me.txtEntidad.Value * 1
'.Offset(0, 3).Value = Me.TextBox1.Value
.Offset(0, 4).Value = Me.txtOficina.Value
.Offset(0, 5).Value = Me.txtControl.Value
.Offset(0, 6).Value = Me.txtCuenta.Value
.Offset(0, 7).Value = s
End With
Call Sort2
Dim ThsBook As Workbook, c As Range, Header As Range, del As Range
Dim lMax
Dim odgovor
Set Header = [K2]
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then 'And Not c.Address = Header.Address Then
MsgBox "Ta vnos je probremenil skupino Št vnosa je : " & s
odgovor = MsgBox("Ali želiš izbrisati ta vnos", vbYesNo)
If odgovor = vbNo Then
Unload Me
Exit Sub
End If
Unload Me
lMax = WorksheetFunction.Max(Columns("H:H"))
Set del = Columns("H").Find(What:=lMax, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
msgbox del.address
del.Offset(0, -7).delete
Exit Sub
End If


ActiveWorkbook.Save
Unload Me
End Sub

[saban]

No it is ok it gives me cell adress where max value resides

Strange?

[saban]

Maybe the problem is when this value is found whole row is selected not just cell with max value??

[ALe]

could be related to "delete"

substitute del.Offset(0, -7).delete with del.Offset(0, -7).value=""

[ALe]

no, I don't think it is related to the selection of the row

[saban]

ok will try that even better cause - if I write delete does it clear also formating and not just values ??

[ALe]

if you write delete the entire cell is removed. It implies that cells to the right are shifted to the left.

if you use .value="" the only thing that happen is that its value turns to ""

[saban]

Thnx man it worked as I wanted

stay cool
Saban

[ALe]

you welcome!

[mdmackillop]

The correct expression is


del.Offset(0, -7).ClearContents



and you can do this all in one wrapped line


Columns("H").Find(What:=lMax, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).Offset(0, -7).ClearContents

[Cyberdude]

This brings up a question in my mind ... what is the difference between
Range("A1").ClearContents
and
Range("A1") = vbNullString
Incidentally, neither removes any formatting.