Select 2 rows of data to cut, once cell containing "1" is found

Question

Ok, so im a very basic user.. Im using the "If" function to find dips in data, when a dip is found column E shows "1", all others are "0". But I need that whole row with the "1" and the next row, even if it has a "0" or "1".

I currently have this: If ActiveCell.Value = "1" Then

Selection.EntireRow.Cut
Sheets("Sheet2").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select

Else

So what I need is to tell it to select the row containing "1" (which it already does), as well as the next row.... the rest should cut and append the data to another worksheet.

W-hit · Answer 1 · 2019-03-15T21:47:38.230

0

Great post on alternatives and more reliable methods than ".Select". After reading, you can adjust your code. How to avoid using Select in Excel VBA

To answer your question, replace

Selection.EntireRow.Cut

with

Range(Selection.EntireRow, Selection.Offset(1, 0).EntireRow).Cut

This should get you a good start, you'll need to add some code to not cut all 5 rows above if some of the are blank because they've already been cut or you could remove blank rows on sheet 2 once this code is done.

Sub GetDipsData()
Dim i As Long
Dim c As Long
Dim LastConsecutiveDip As Long
Dim vLastRow As Long

Sheets("Sheet1").Activate
vLastRow = Cells(Rows.Count, "E").End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To vLastRow
    If Cells(i, "E") = 1 Then
        s2LastRow = Sheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row
        For c = i + 1 To vLastRow
            If Cells(c, "E") = 1 Then
                LastConsecutiveDip = c
            Else
                Exit For
            End If
        Next
        If c <> i + 2 Then
            'copy 5 above and 5 below
            If i < 6 Then
                Range(Rows(2), Rows(c).Offset(5, 0).EntireRow).Cut Sheets("Sheet2").Range("A" & s2LastRow)
            ElseIf c + 5 > vLastRow Then
                Range(Rows(i).Offset(-5, 0), Rows(vLastRow).EntireRow).Cut Sheets("Sheet2").Range("A" & s2LastRow)
            Else
                Range(Rows(i).Offset(-5, 0), Rows(c).Offset(5, 0).EntireRow).Cut Sheets("Sheet2").Range("A" & s2LastRow)
            End If
            i = c + 5
        Else
            'just copy 2 rows
            If i + 1 > vLastRow Then
                Rows(i).Cut Sheets("Sheet2").Range("A" & s2LastRow)
            Else
                Range(Rows(i), Rows(i).Offset(1, 0).EntireRow).Cut Sheets("Sheet2").Range("A" & s2LastRow)
                i = i + 2
            End If
        End If
    End If
Next

Application.ScreenUpdating = True

End Sub

edited Mar 15 '19 at 21:47

answered Mar 15 '19 at 19:00

W-hit

353
3
14

Thank you, that did solve my problem but that lead to another problem. May I ask help on a more lengthily problem? – Tats Mar 15 '19 at 19:19
go ahead, hopefully I can help – W-hit Mar 15 '19 at 19:22
OK, so.... I have 30 000+ rows of data with the "If" function looking for drops in data, but the data does vary and drop and rise, but every now and then there is a pattern of drops which is what im after. So I would like the macro to find in column E, when "1" appears 3 times consecutively, to select from the first "1" and 5 rows above, to the last "1" and 5 rows below that. Essentiall I want to take a big bite of the data, some above, some below and the main stuff inbetween. This I will then cut to another worksheet. – Tats Mar 15 '19 at 19:28
This needs to repeat continuously for all 30 000+ rows or however many rows the data set requires – Tats Mar 15 '19 at 19:30
added to my answer above. – W-hit Mar 15 '19 at 21:47
Thank you, I tried it out and it is cutting the 5 rows, but its cutting every 5 rows and that code looks so complicated I dont know where to make the changes. Is there a way for em to send you a section of my data so you can practice with?? – Tats Mar 16 '19 at 08:29
W-hit, I think I found a mor simplified answer thanks to your macro. Instead of doing to more difficult way, in another column I added up 5 cells, if that value is 5 (meaning there is a constant drop in data) that 20 rows of data beneath that 5 are cut and pasted into the other worksheet. This gives me most of my chunk of data. – Tats Mar 16 '19 at 14:59

Select 2 rows of data to cut, once cell containing "1" is found

1 Answers1