Find/Cut/Paste Last Row

Strobbe

New Member
Joined
Jul 18, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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")
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RobP

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
Hi,
thanks for the reply. See if this works for you

VBA Code:
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
 
Solution

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,129
Office Version
  1. 365
Platform
  1. Windows
@RobP You forgot to add wild cards to the CountIfs ;)

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

RobP

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,176,395
Messages
5,902,822
Members
434,998
Latest member
BradneedsXcellhelp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top