0

My question is similar to this one VBA - Find first and last row containing specific text but I need a little tweak. My Column A values are Delete, Delete end, and -(dash) only and I need to get the first rows where Delete is and last rows where Delete end is.

Rows    Column A
1       Delete
2       Delete
3       Delete end
4       -
5       -
6       -
7       -
8       Delete
9       Delete end

Edit: I would like to extract the rows 1 and 3, and 8 and 9, not the range like A1:A3. How should I do this approach?

Community
  • 1
  • 1
Meltdowner
  • 45
  • 1
  • 10
  • What do you mean by extracting rows? Do you want to address these ranges in your code? Do you want the row numbers? Do you want to write the text "A1:A3" in another cell? – Sun Jun 15 '16 at 07:54
  • Ohh, I meant the row numbers, not the range "A1:A3", so should be 1 and 3. Was too focused on looking at the columns. Sorry. – Meltdowner Jun 15 '16 at 08:04

1 Answers1

2

Hope this is what you want.

Assuming your data starts with A2..

Sub test()
    Dim lastrow As Long, i As Long
    Dim startaddress As String, endaddress As String, alladdress As String
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If Cells(i, 1) = "Delete" Then
            startaddress = Cells(i, 1).Address
            Do While Cells(i, 1) <> "Delete end"
                i = i + 1
            Loop
            endaddress = Cells(i, 1).Address
            If alladdress <> "" Then
                alladdress = alladdress & ", " & startaddress & ":" & endaddress
            Else
                alladdress = startaddress & ":" & endaddress
            End If
        End If
    Next i
    Cells(2, 2) = alladdress
End Sub

enter image description here

Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25
  • Hi, thank you for your answer, but there was a mistake in my question. I wanted the row numbers, not the range. In that case, should I just change startaddress = Cells(i, 1).Address to .Rows? – Meltdowner Jun 15 '16 at 08:10
  • I got it now, thanks. Just changed startaddress and endaddress to .Row. – Meltdowner Jun 15 '16 at 08:27