0

Im a huge fan of VBA, however, lack some big amount of knowledge in order to do this. Tried to find and put together some codes i managed to get in google, however with no success.

The "thing" :

  • have lots of data from Columns A till D, down to row 20000(ish, value is always changing)

I've been trying to find a way of starting in A1 and find partial cell with "Alpah" and keeping the row , finding partial cell with "Beta" and keeping row , After that delete all rows between . Next, Keep "Beta" row and all data below till the next "Alpah" . btw, i do have a few blanks on the way

If needed i can post a small exmple

A1 always starts with the same partial cell value (Alpha)

Edgar.

Community
  • 1
  • 1
EdN
  • 41
  • 1
  • 11
  • 2
    A sample would be ideal. Also, any sample code that you have put together already that didn't work would be best. That you tried and weren't able to solve it will get more responses on how to correct it than simply asking someone to write the code from scratch for you. – Rodger Jun 14 '16 at 09:42

1 Answers1

1

EDITED I've misunderstand your problem. Try this:

Private Sub DeleteRow()
Dim iAlpha As Integer
Dim iBeta As Integer
Dim lLastRow As Long
Dim k As Integer, i As Integer

lLastRow = Worksheets("MyDataSheet").Cells(65536, 1).End(xlUp).Row

k = 1

For i = 1 To lLastRow
    If InStr(Worksheets("MyDataSheet").Cells(k, 1).Value, "Alpha") > 0 Then
        iAlpha = k 'get row containing Alpha
    ElseIf InStr(Worksheets("MyDataSheet").Cells(k, 1).Value, "Beta") > 0 Then
        iBeta = k 'get row containing Beta
        For j = (iAlpha + 1) To (iBeta - 1) 'Delete rows between Alpha and Beta
            Worksheets("MyDataSheet").Rows(iAlpha + 1).EntireRow.Delete
        Next
        k = iAlpha + 1
    End If
    k = k + 1
    lLastRow = lLastRow - iBeta + iAlpha + 1
Next
End Sub
CMArg
  • 1,525
  • 3
  • 13
  • 28
  • Wow, I was way over complicating my answer. This is much more elegant. – Rodger Jun 14 '16 at 12:04
  • Yep, my former code was elegant but it didn't answer the question :). Now it is not so elegant, but I think is doing the job. – CMArg Jun 14 '16 at 12:31
  • 1
    I assumed it did answer it. I suppose if there are other alphas after the first alpha but before the first beta, this will delete them also, compared to your previous code where you just deleted any line with neither an alpha nor a beta. I had assumed that wouldn't be a good thing, but this is more what they asked for, yes. – Rodger Jun 14 '16 at 12:37
  • 1
    @Rodger You're right. But before further modifying the code, it would be better to get more details from EdN, to properly state what he wants. – CMArg Jun 14 '16 at 12:46
  • Hey guys, thks for the answer and also for the provided code. – EdN Jun 14 '16 at 13:16
  • @EdN If answer does not fit your needs, please add pertinent info for us to provide a better code. If it does, please mark as accepted. – CMArg Jun 14 '16 at 13:37
  • Sry, couldnt write, was in another terminal testing it ^^ :) so, nothing happens.... i've copy the code, run it, no error nor anything... don't know if it because cell value only contains partial word Alpha or Beta. – EdN Jun 14 '16 at 14:23
  • Yep, only applies to cell value = alpha or beta... is it possible to adjust to a if cointains? thks for the patience and help so far – EdN Jun 14 '16 at 14:29
  • Replace `If Worksheets("MyDataSheet").Cells(k, 1).Value = "Alpha" Then` with `If InStr(1, Worksheets("MyDataSheet").Cells(k, 1).Value, "Alpha") > 0 Then`. Same for Beta. Take a look at [this](http://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string). – CMArg Jun 14 '16 at 14:40
  • Wow, awesome, takes a bit, due to huge quantity of data (i assume) but it gets the job very well done! Thk you very much for the help!!!! BR, Edgar. – EdN Jun 14 '16 at 15:01
  • @EdN Depending on your data, maybe you can reduce the loop a bit, since each time you delete rows, the last row is also reduced. See the recalculation of `lLastRow` after row deletion in my edited code. And mark as accepted if code works for you! (thats all we get for the help, appart from feeling good for helping...) – CMArg Jun 14 '16 at 15:21
  • And for quicker code execution, consider `Application.ScreenUpdating = False` and `Application.ScreenUpdating = true`. Look [here](http://stackoverflow.com/questions/12391786/effect-of-screen-updating). – CMArg Jun 14 '16 at 15:26
  • Thks Team :) huge help!! – EdN Jun 15 '16 at 10:18