0

I did research this question but could not find the specific answer I was looking for and am actually even more confused at present.

I created a macro that would run through rows on a sheet and run boolean checks on a number of cells in each row that looked for the presence or absence of specific values, or calculated the outcome of a specific inequality. On the basis of those checks, the macro may or may not pass the row number into a specific array. That part is working fine.

My issue is, now that I have the row numbers (stored in variant arrays) - I cannot figure out how to properly concatenate that data into a range and then take a bulk excel action on those items. What I'd like to do is create a range of those values and then delete all of those rows at once rather than looping through.

My macro is on my work computer, but here's something I wrote that should explain what I'm trying to do:

    Sub Test()
Dim Str As String
Dim r As Range
Dim i, a As Integer
Dim Count As Integer
Dim RngArray()



Count = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "A:A").End(xlUp).Row
ReDim RngArray(Count)


a = 0
For i = 1 To Count
    If Not i = Count Then
        RngArray(a) = i
        Str = Str & RngArray(a) & ":" & RngArray(a) & ", "
        a = a + 1
    ElseIf i = Count Then
        RngArray(a) = i
        Str = Str & RngArray(a) & ":" & RngArray(a)
        a = a + 1
    Else: End If
Next i

Set r = Range(Str)'Error Can Appear here depending on my concatenation technique
Range(Str).EntireRow.Delete 'error will always appear here

End Sub

I've combined a few steps here and left out any Boolean checks; in my actual macro the values in the arrays are already stored and I loop from LBound to UBound and concatenate those values into a string of the form ("1:1, 2:2, 3:3, ...., n:n")

The reason why I did this is that the rows are all over the sheet and I wanted to get to a point where I could pass the argument

Range("1:1, 2:2, 3:3, ..., n:n").EntireRow.Delete

I think it's clear that I'm just not understanding how to pass the correct information to the range object. When I try to run this I get a "Method Range of Object Global" error message.

My short term fix is to just loop through and clear the rows and then remove all of the blank rows (the macro keeps track of absolute positions of the rows, not the rows after an iterative delete) - but I'd like to figure out HOW to do this my way and why it's not working.

I'm open to other solutions as well, but I'd like to understand what I'm doing wrong here. I should also mention that I used the Join() to try to find a workaround and still received the same type of error.

Thank you.

5
  • That Range syntax should work. I tried it myself too just to be sure. My code was range("1:1,3:3,5:5").EntireRow.Delete and it deleted those rows happily. Commented Jan 25, 2018 at 5:18
  • Is that the full error message? Commented Jan 25, 2018 at 5:19
  • The full message is: Run-time error '1004': Method 'Range' of object'_Global' failed When I type in the values using that syntax, yes, it works perfectly fine but for some reason it won't accept it as a pre-made string with identical syntax Commented Jan 25, 2018 at 5:29
  • Can you try fully qualifying the range: ThisWorkbook.Sheets("Sheet1").Range(...? Without it should reference the ActiveSheet if it's in a standard module, or the parent sheet if it's in a sheet's module but I don't know what your context is or where your focus is (which might affect this). Best to fully qualify the range to be sure. Commented Jan 25, 2018 at 5:55
  • Ah, I figured it out. I'll post my solution. Thank you so much for your help, I appreciate it! The error message is still a little strange to me. Commented Jan 25, 2018 at 6:11

1 Answer 1

1

After some experimentation with my dataset for the macro above, I discovered that it worked on small sets of data in A:A but not larger sets.

I ran Debug.Print Len(Str) while tweaking the set size and macro and found that it appears Range() can only accept a maximum of 240 characters. I still don't understand why this is or the reason for the specific error message I received, but the macro will work if Len(Str) < 240.

I'll have to loop backwards through my array to delete these rows if I want to use my present method...or I may just try something else.

Thanks to Andrew for his attention to this!

Sign up to request clarification or add additional context in comments.

1 Comment

Ah I didn't think of that. Good catch. Yes in that case you'll have to delete them one by one (iterating backwards while deleting will make it easier than blanking and deleting blanks).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.