Find Cell then Cut Rows

Resolved · Medium Priority · Version 2010

Thu 23 Jun 2016, 08:59 Delegate Mark said...

Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course

Find Cell then Cut Rows

Hi,

I'm trying to write some code where I need to find a cell that contains "M002" and then cuts the first row that it appears in through to the next 30 rows.

The difficulty I'm having is that I only want to search for M002 in column A as it appears elsewhere in the document.

My plan was to insert a MATCH formula into the document to find where in the list M002 appears and then to count the rows down from the cell with the MATCH formula before selecting 35 rows and cutting.

Any help is appreciated.

Mark

Thu 23 Jun 2016, 11:32 Trainer Jens said...

RE: Find Cell then Cut Rows

Hi Mark,

Thank you for the forum question.

Please have a look at my code below. I hope this is what you need.

Sub CutRows()

Range("a:a").Find(what:="M002").Select
Selection.Range(Rows(1), Rows(30)).Cut

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Separate the year from a date

To separate the year from a date use the =year() function, eg a date is in cell A1 and in A2 you wish to display the year enter the function =year(A1)

View all Excel hints and tips