VBA - Run-time error 1004 (application-defined or objecy-defined error)-CodePudding

The code is not working in this way, but if i use ('Wks_So.Range("A9:A150").ClearContents), instead of (Wks_So.Range("A9", Range("A9").End(xlDown)).ClearContents), the code has no problem at all.

My aim for the selection is to clear all the contents from A9 to the last cell of column A with values

Dim i As Integer
Dim j As Integer
Dim x As Integer

Application.Calculation = xlCalculationManual

Set Wks_Sb = Worksheets("Scarico_Bond")
Set Wks_So = Worksheets("Scarico_Other")
Set Wks_I = Worksheets("Invio")

Wks_Sb.Range("A9", Range("A9").End(xlDown)).ClearContents
'Wks_Sb.Range("A9:A150").ClearContents
Wks_So.Range("A9", Range("A9").End(xlDown)).ClearContents

here is the error

'Wks_So.Range("A9:A50").ClearContents
Wks_Sb.Range("D9:D140").Interior.Color = vbWhite

j = 9
k = 9
x = 8

For i = 7 To 150

    If InStr(1, (Wks_I.Cells(i, 4).Value), "Obbligazioni") > 0 Then
        
        Wks_Sb.Cells(j, 1) = x - 7
        j = j   1
        
        Else
            
            If InStr(1, (Wks_I.Cells(i, 4).Value), "Fondi/ETF") > 0 Then
            
                Wks_So.Cells(k, 1) = x - 7
                k = k   1
        
            End If
    End If

    x = x   1

Next i
    
Application.Calculation = xlCalculationAutomatic

End Sub

CodePudding user response：

I would do like this and also define some variables:

Sub yourSub()
Dim i, j, k, x As Integer
Dim lastRow_Sb, lastRow_So, lastRow_I as Integer
Dim wb As Workbook
Dim Wks_Sb, Wks_So, Wks_I As Worksheet
Set wb = ActiveWorkbook
Set Wks_Sb = wb.Worksheets("Scarico_Bond")
Set Wks_So = wb.Worksheets("Scarico_Other")
Set Wks_I = wb.Worksheets("Invio")
lastRow_Sb = Wks_Sb.Cells.SpecialCells(xlLastCell).Row
lastRow_So = Wks_So.Cells.SpecialCells(xlLastCell).Row
lastRow_I = Wks_I.Cells.SpecialCells(xlLastCell).Row

Application.ScreenUpdating = False
Wks_Sb.Range("A9:A" & lastRow_Sb).ClearContents
Wks_So.Range("A9:A" & lastRow_So).ClearContents
Wks_Sb.Range("D9:D140").Interior.Color = vbWhite

j = 9
k = 9
x = 8

For i = 7 To lastRow_I 'Or should this always be 150?
    If InStr(1, (Wks_I.Cells(i, 4).Value), "Obbligazioni") > 0 Then
        Wks_Sb.Cells(j, 1) = x - 7
        j = j   1
    Else
        If InStr(1, (Wks_I.Cells(i, 4).Value), "Fondi/ETF") > 0 Then
            Wks_So.Cells(k, 1) = x - 7
            k = k   1
        End If
    End If
    x = x   1
Next i
Application.ScreenUpdating = True
End Sub

CodePudding user response：

You should use End(xlUp) instead of twice End(xlDown):

Change

Wks_Sb.Range("A9", Range("A9").End(xlDown)).ClearContents
'Wks_Sb.Range("A9:A150").ClearContents
Wks_So.Range("A9", Range("A9").End(xlDown)).ClearContents

to:

Wks_Sb.Range("A9", Wks_Sb.Range("A1000000").End(xlUp)).ClearContents