Copying a row in Excel using VBA

[sarah2010]

Hi folks,
I got an excel file which has a bunch of data. I would like to find the items in column "A" which has the value "sarah" & copy the entire row & paste it in a new file. Attached is the sample file.
Note: The pattern of the data (in column A) need not be having a same interval. Can somebody help me out with this? Thanks

[GTO]

Greetings Sarah,

I see that you just joined:) Welcome to vbaexpress!


...The pattern of the data (in column A) need not be having a same interval.

I'm sorry, but I didn't quite understanf that last bit. Will 'sarah' always be at the start of the string?

Mark

[sarah2010]

Hey Mark! thanks for welcoming me to the forum!
I meant to say that the pattern in which the data in column A which has the value "sarah" need not be of uniform interval. like say, it may exist once in every 3 rows or 4 or anything. so, the interval will be random. Also, "sarah" need not be the starting string always. :help . Thanks for your attention!

[GTO]

If "sarah" may be anyplace (start, mid, end) in the string in the cell, let's try using InStr (In String). See VBA help for a description.

You could also use AutoFilter with "contains", but I chose just to loop thru the rows.

In a Standard Module:


Option Explicit

Sub exa()
Dim _
wksDest As Worksheet, _
wbDest As Workbook, _
rngSource As Range, _
lRow As Long, _
lCol As Long, _
i As Long, _
ii As Long

'// Set a reference to a newly created one-sheet workbook. //
Set wbDest = Workbooks.Add(xlWBATWorksheet)
'// Set a reference to the sheet within. //
Set wksDest = wbDest.Worksheets(1)

'// Change source sheetname to suit. //
With ThisWorkbook.Worksheets("Sheet1")
'// Presumes we are safe in using the last cell in Col A to set the bottom //
'// row in our range, and uses the last Col in row 1 with data to set //
'// the rightmost col in our range of interest. //
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

'// Set a reference to the range we want to look thru. Actually, I was //
'// writing a bit fast, this could easily be just Col A, as we are only //
'// looking for "sarah" there... //
Set rngSource = .Range(.Range("A2"), .Cells(lRow, lCol))

'// Copy the header row.. //
.Range("A1").EntireRow.Copy wksDest.Range("A1")
ii = 1

For i = 1 To rngSource.Rows.Count
'// Use InStr to see if "sarah" is anyplace in the string in each cell //
If InStr(1, rngSource(i, 1).Value, "sarah", vbTextCompare) > 0 Then
'// Use a counter to keep track of where we need to paste the next //
'// row.
ii = ii + 1
rngSource(i, 1).EntireRow.Copy wksDest.Range("A" & ii)
End If
Next
End With
End Sub

Hope that helps,

Mark

[sarah2010]

:thumb Thanks Mark, it works to my expectation in the sample file. I'll test it in real case scenarios & let you know. Thanks again for your time & help!!!

[sarah2010]

Hey Mark, thanks a lot for the help. It works fine with the real case scenerios with minor tweaks. Sincerely apreciate your time!
:beerchug:

[GTO]

:thumb Glad to be of help, and again, welcome to the forum:hi: