Select Range up until specific string VBA

Question

I'm working on a spreadsheet that formats a report pulled externally from an application. Most of my formatting is done, but for one portion of it I just need to select all cells in range A9:IZ where Z is the number of rows up until a specific string (not including the string itself).

Here is an example sheet. I need everything from A1:I649 selected, it's the row right above cell A650, which always has the string "***Total" in it. This report is dynamic, so changes length every day, but I formatted it so that ***Total always appears at the bottom. I just can't figure out how to select everything up until it for the next part of my script. I've already set up the rest of my code, I just need to nail this portion of it.

I made a shorthand image for a visual. In this case, I need A10:I30 to be my selected range, as it's the row right before ***Total

Just need it selected, because the rest of my script handles the rest of the formatting.

SJR · Accepted Answer · 2018-10-04 16:41:14Z

1

try,

dim rng as range, m as variant

with worksheets("sheet1")
    m = application.match("~*~*~*total", .range("I:I"), 0)
    if not iserror(m) then
        set rng = .range(.cells(9, "A"), .cells(m - 1, "I"))
        debug.print rng.address(0, 0)
        rng.select
    end if
end with

Note the escape characters (~ or tilde) that are used to remove the wildcard characteristics of the asterisks.

edited Oct 4, 2018 at 16:41

SJR

23.1k6 gold badges21 silver badges26 bronze badges

answered Oct 4, 2018 at 15:43

user4039065

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

3 Comments

TheVBNoobie Over a year ago

Getting a Synthax compile error on the line: "rng select" I tried changing it to rng.select and it didn't do anything.

TheVBNoobie Over a year ago

Tried debugging a little by commenting out the if and end if lines, I got a "Type Mismatch" error on the "set rng = .range(.cells(9, "A"), .cells(m - 1, "I"))" line. Also altered the "~*~*~*total" part into "~*~*~* Total" since I didn't account for the space originally. It still didn't select anything though.

TheVBNoobie Over a year ago

Managed to solve it, but marking this as correct to close it out. Not sure why it didn't work, it looks pretty solid and a lot c leaner than what I came up with, but it just wasn't selecting anything at all. Thank you though, maybe it was something weird on my end.

TheVBNoobie · Accepted Answer · 2018-10-04 19:01:24Z

I managed to solve it. It's probably a bit sloppy but it works:

Sub LeanCut()
Dim lrow As Long
'This part is finding the cell with "~~~ Total" and deleting it and everything below       
Cells.Find(What:="~*~*~* Total", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
        Rows(ActiveCell.Row & ":" & Rows.Count).Delete
'This part just selects everything from A10:I (last row with data)
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A10:I" & lrow).Select

End Sub

Like I said, it's a bit sloppy but I'm not the best with VBA by any means, and it does what I intended. Just makes ***Total into an empty row and selects everything that has values in it from A10 down.

Collectives™ on Stack Overflow

Select Range up until specific string VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related