Find/Cut/Paste Last Row

[Strobbe]

Hello,

Currently trying to create a Macro that will find "IBT" in my data set from Sheet2 then select the Current Region to cut that data to Sheet3.

currently i am able to find that "IBT" select current region cut and paste to Sheet3, however cannot figure out how to loop this to grab all data that contains the "IBT"


Dim lastrow As Long

lastrow = Worksheets("Sheet3").Range("A5000").End(xlUp).Row

Cells.Find(What:="IBT", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

ActiveCell.CurrentRegion.Select
Selection.Cut Worksheets("Sheet3")

 

[RobP]

Hi,

Do we assume "IBT" is a text, alone in a cell in your data set ? Where is your dataset located (rows / cols ?), and does the dataset change ? Is your data all in one column ? Is IBT always in the same column?

From what you say, we assume you want to Cut the data from Row 1 to the row that contains IBT ? or do you just want to copy all the data above it to sheet 3 ?

lots of questions, means we could use a little more info from you if possible please ?

thanks
Rob

 

[Strobbe]

Thanks for the quick reply!

“IBT” is text that I am trying to find from transferring a text file into Excel - it is not alone in the cell.

IBT is in column D - first one is found at row 192 and then want to select all surrounding data which was rows 190-195 and cut that to A1 of Sheet3.

The dataset that would upload into Excel will always change and will need to use the Find feature to locate which cell has IBT in order to select the rows surrounding it.

Due to the file being a text file I copy/pasted the file into column A on Sheet2 and then used Text to Columns with this "Step 1" macro

Step 1 Format Sheet

Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1)), TrailingMinusNumbers:=True
Range("A:A").Delete

End Sub

I only want to Cut the data that contains the IBT within the 5 rows and then paste them to Sheet3 - trying to have it find the last row unused +1 so there is a space in between each cut/paste

Was able to get it to find the first IBT section and paste onto Sheet3 but i cant figure out how to loop it and find the next row +1

 

[RobP]

Hi,

thanks for the reply. Still not clear on your data sorry to say. So IBT is always in column D, and it has also got other stuff inside the cell (can you show an example of the data with IBT inside it ?

If you were a computer, rather than a human - how would you know that you want to copy a few of the rows around the initial find in row 192 ? ie. do you always copy the 2 preceding rows, and the 3 rows after it (for whatever reason ?) or could this differ ? If so, how do you know which rows you want ?

It appears you have 14 columns of data (so your last column is "N" , is that correct ?)

sorry to ask more questions, but computers need specifics ..
thansk
Rob

 

[Strobbe]

Hello, no worries at all.

i used the Current Region to grab all surrounding cells with data in them as the text file already had each block of data separated by an empty row.

IBT will look something like this - IBTS-wv#34229 or IBTR-vw#34430

It does look like the data is always in the middle row of the 5 so 2 up and 2 down
- not 100% if this could change thats why I used Current.Region

Data goes until column M so 13

 

[RobP]

Hi,
thanks for the reply. See if this works for you

Sub test()


Dim lastrow,  i, x As Long

lastrow = Worksheets("Sheet3").Range("A5000").End(xlUp).Row

x = WorksheetFunction.CountIf(Columns(4), "IBT") 'count how many "IBT" instances there are first

For i = 1 To x

    Sheets("Sheet1").Range("D1").Activate 'ensures cursor is reset back to sheet 1 column D

    Columns(4).Find(What:="IBT", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    ActiveCell.CurrentRegion.Select
    Selection.Cut Destination:=Worksheets("Sheet3").Range("A" & lastrow + 1)
   
   
Next i

End Sub

 

[Alex Blakenburg]

@RobP You forgot to add wild cards to the CountIfs

x = WorksheetFunction.CountIf(Columns(4), "*IBT*") 'count how many "IBT" instances there are first

 

[RobP]

thanks Alex,

it seemed to work fine when I tested it before - and now I realised why - its because I just used "IBT" alone in my test column .. oops.

@*strobbe - indeed you need to correct the line above first.

 

[Strobbe]

Thanks Alex & Rob,

Almost have it - it was able to successfully know how many "IBT" there were and cut and paste to Sheet3 but it kept putting them all in the same spot.

So Sheet3 is just the last one instead of a list of them. Each data set started at A2 and then pasted over each other. Something wrong with the lastrow

 

[RobP]

Hi, sorry, move the line of code that says ‘lastrow =‘ etc. to the 1st row inside the for loop instead.

Hopefully it will get reset every time then.
Cheers
Rob