Code checking/helping

Question

can someone please look into my code and say me where is the mistake cause I got a type mismatch error message ? With this code I would like to delete all rows who which contain "0" in the respective cells.

I got the error message for the line where is standing: sn = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), [transpose(row(1:8))]) Also I had to declare the variable "c00" and I choosed "c00 As Variant". I don't know if it its correct. I would appreciate someone helping me to solve the problem.

Dim sn As Variant, c00 As Variant

   sn = Sheets(1).UsedRange

   For j = 1 To UBound(sn)
     If sn(j, 4) & sn(j, 5) & sn(j, 6) & sn(j, 7) & sn(j, 8) & sn(j, 9) = "000000" Then c00 = c00 & "|" & j
   Next

     If c00 <> "" Then
        sn = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), [transpose(row(1:8))])
            Sheets(1).UsedRange.ClearContents
            Sheets(1).Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
     End If

Original Code

Dim LR%


LR = Cells(Rows.Count, 3).End(xlUp).Row 

Set Myrange = Range("D2:AO" & LR).SpecialCells(xlCellTypeBlanks) 'nur Leerzellen
Myrange.Formula = "0"


ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange

lastrow3 = r.Rows.Count + r.Row - 1

    For j = lastrow3 To 1 Step -1
        If (Cells(j, 4) = 0 And Cells(j, 5) = 0 And Cells(j, 6) = 0 And Cells(j, 7) = 0 And Cells(j, 8) = 0 And Cells(j, 9) = 0) Then
            Rows(j).Delete
        End If
    Next j

Image w/ Error

There is an "upvote" arrow just to the left of a comment, if you hover your mouse over a comment. Such upvotes don't appear in any statistics, or give points, though. — Cindy Meister, May 15 '18 at 16:03
@CindyMeister Although I believe that the upvoting may require some minimum reputation to do.... — Mistella, May 15 '18 at 16:05
@CindyMeister Thank you, but there is no vote sign shown, only for the other answers/questions. — Bamane, May 15 '18 at 17:21
@Bamane I just edited my answer to include the final conclusion and link. You can upvote that, or accept it, if you believe it answers your question pretty well. — Mistella, May 17 '18 at 15:06
@Bamane I also believe that we should clean up this Q&A a bit; so I've suggested an edit to your question with the pertinent information from your "answers". — Mistella, May 17 '18 at 15:12
@Mistella Thanks a lot for your support. I'll try my best to understand the processes here. — Bamane, May 17 '18 at 15:14

Mistella · Answer 1 · 2018-05-17T15:00:33.763

Edit: the error was from attempting to use Application.Index on an array larger than the function size limit. Redirect to here for Q&A an on alternative option to Application.Index.

I'll break down my analysis of your code:

Application.Index(Array, Row_Number, Column_Number)

The code you currently have:

sn = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), [transpose(row(1:8))])

is saying that the parameters are:

Array: sn
Row_Number: Application.Transpose(Split(Mid(c00, 2), "|"))
Column_Number: [transpose(row(1:8))]

The Array section looks fine to me. The Row numbers will, I think(?), be the values for j which you collected in c00 (although the Application.Transpose may not be necessary Correction: it is in this scenario.). I have no idea what is going on with your Column_Number parameter....

Issues:

Application.Index keeps the selected columns/rows. However, your if statement selects the values of j where the rows are entirely 0, so instead of losing them, you would be keeping only those rows.

If your intention is to keep all the columns, you can just input 0 into the Column_Number parameter. Correction: this works when only selecting a single row to keep. If selecting multiple rows, all columns must be listed as well.

Corrected code:

Since this code does delete data, you should save a copy of the data before running this code on it.

Note: c00 can be a Variant; String also works. You will need to also copy over the fillA function, as well.

Dim sn As Variant, c00 As String

   sn = Sheets(1).UsedRange

   ' Changed condition based on your post with previous code. (And negated)
   For j = 1 To UBound(sn)
     If Not ((Cells(j, 4) = 0 And Cells(j, 5) = 0 And Cells(j, 6) = 0 And Cells(j, 7) = 0 And Cells(j, 8) = 0 And Cells(j, 9) = 0)) Then c00 = c00 & "|" & j
   Next

     If c00 <> "" Then
        ' Corrected inputs for Application.Index, Added helper function "fillA".
        sn = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), fillA(1, UBound(sn, 2) - LBound(sn, 2) + 1))
            Sheets(1).UsedRange.ClearContents
            Sheets(1).Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
     End If

Function fillA(min As Long, max As Long) As Variant
    Dim var() As Variant, i As Long
    ReDim var(1 To max - min + 1)

    For i = min To max
        var(i) = i + min - 1
    Next i
    fillA = var
End Function

Edit:

Realized this did not address your issue. I suspect that the error was from the [transpose(row(1:8))] you were inserting for the Column_Number parameter. Maybe someone else has a simpler way of doing what I did with the fillA function (what I believe you were attempting).

Code checking/helping

1 Answers1