MS Excel / VBA help thread

You are reading a single comment by @aggi and its replies. Click here to read the full conversation.

•

Sam_w

I'm hoping someone can help me with a bit of VBA code, which for some reason I am struggling with.
I am looking to select a range in excel.

The starting row is row 15 (this is fixed, won't change)
The starting column contains the word "Coefficient" in row 15

The end row contains the word "Xaxis" in column A
The end column is the same as the starting column

There is not continuous data in the column I am selecting so can't use End(XLDown).

This feels like it should be really simple, but I can't get my head round it, Help!!

•

aggi in reply to @Sam_w

I think you can just use match. I'm not in front of a computer to test but something like

 EndRow = Application.WorksheetFunction.Match("xaxis", Range("A:A"), 0)

If the cell has other stuff than just the word xaxis and you're on the latest version of Excel then you can use xmatch with a wildcard.

EDIT: Actually Match also has a wildcard function. This should do it:

Sub Macro1()

Dim endcol As Long
Dim endrow As Long

Dim rowrng As Range
Set rowrng = Application.Range("A:A")
Dim colrng As Range
Set colrng = Application.Range("15:15")

endrow = Application.Match("*Xaxis*", rowrng, 0)
endcol = Application.Match("*Coefficient*", colrng, 0)
Range(Cells(15, 1), Cells(endrow, endcol)).Select
    
    
End Sub

MS Excel / VBA help thread

About

LFGSS