rows contains certain text "?" in new document

leewalker · Jul 21, 2013

hi

i wonder if anyone can help me i have a spreadsheet with 12 columns

i am looking for a way in which i can search the whole spreadsheet row by row for "?" in the last columns and if found select the whole of the row and copy the whole row to a line of a spreadsheet of set name i require it to leave 1 row under each row it copies. until it get to the end of the spreadsheet around 4000+ lines.

as you can see if i was to do this manually would take hours upon hours.

i hope i have outlined my problem clearly if you have any more questions please do not hesitate to asked and i be happy to explain more best i can

i thank you all in advance.

shg · Jul 21, 2013

Have you tried recording a macro?

leewalker · Jul 21, 2013

shg said:
Have you tried recording a macro?

Was hoping for some custom via code that would allow me to search whole workbook unsure how this could me done with macro

shg · Jul 21, 2013

Recording a macro is a place to start. It won't do everything you need, but someone can help you clean it up, and you'll learn something in the process.

markmzz · Jul 21, 2013

If I undertand correctly what you want, try this array formula (use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example):

Code:

A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(Sheet01!A$2:A$25,SMALL(IF(ISNUMBER(SEARCH("~?",Sheet01!$L$2:$L$25)),
ROW(Sheet01!$L$2:$L$25)-ROW(Sheet01!$L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

Sheet01

Col01	Col02	Col03	Col04	Col05	Col06	Col07	Col08	Col09	Col10	Col11	Col12
DataCol012	DataCol022	DataCol032	DataCol042	DataCol052	DataCol062	DataCol072	DataCol082	DataCol092	DataCol102	DataCol112	DataCol122
DataCol013	DataCol023	DataCol033	DataCol043	DataCol053	DataCol063	DataCol073	DataCol083	DataCol093	DataCol103	DataCol113	DataCol123?
DataCol014	DataCol024	DataCol034	DataCol044	DataCol054	DataCol064	DataCol074	DataCol084	DataCol094	DataCol104	DataCol114	DataCol124
DataCol015	DataCol025	DataCol035	DataCol045	DataCol055	DataCol065	DataCol075	DataCol085	DataCol095	DataCol105	DataCol115	DataCol125
DataCol016	DataCol026	DataCol036	DataCol046	DataCol056	DataCol066	DataCol076	DataCol086	DataCol096	DataCol106	DataCol116	DataCol126?

Sheet02

Col01

Col02

Col03

Col04

Col05

Col06

Col07

Col08

Col09

Col10

Col11

Col12

Col13

BlankRow

DataCol013

DataCol023

DataCol033

DataCol043

DataCol053

DataCol063

DataCol073

DataCol083

DataCol093

DataCol103

DataCol113

DataCol123?

BlankRow

DataCol016

DataCol026

DataCol036

DataCol046

DataCol056

DataCol066

DataCol076

DataCol086

DataCol096

DataCol106

DataCol116

DataCol126?

Markmzz

leewalker · Jul 22, 2013

markmzz said:
If I undertand correctly what you want, try this array formula (use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example):

Code:

A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(Sheet01!A$2:A$25,SMALL(IF(ISNUMBER(SEARCH("~?",Sheet01!$L$2:$L$25)), ROW(Sheet01!$L$2:$L$25)-ROW(Sheet01!$L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

Sheet01

Col01
Col02
Col03
Col04
Col05
Col06
Col07
Col08
Col09
Col10
Col11
Col12

DataCol012
DataCol022
DataCol032
DataCol042
DataCol052
DataCol062
DataCol072
DataCol082
DataCol092
DataCol102
DataCol112
DataCol122

DataCol013
DataCol023
DataCol033
DataCol043
DataCol053
DataCol063
DataCol073
DataCol083
DataCol093
DataCol103
DataCol113
DataCol123?

DataCol014
DataCol024
DataCol034
DataCol044
DataCol054
DataCol064
DataCol074
DataCol084
DataCol094
DataCol104
DataCol114
DataCol124

DataCol015
DataCol025
DataCol035
DataCol045
DataCol055
DataCol065
DataCol075
DataCol085
DataCol095
DataCol105
DataCol115
DataCol125

DataCol016
DataCol026
DataCol036
DataCol046
DataCol056
DataCol066
DataCol076
DataCol086
DataCol096
DataCol106
DataCol116
DataCol126?

<tbody>
</tbody>

Sheet02

Col01 Col02 Col03 Col04 Col05 Col06 Col07 Col08 Col09 Col10 Col11 Col12 Col13
BlankRow
DataCol013 DataCol023 DataCol033 DataCol043 DataCol053 DataCol063 DataCol073 DataCol083 DataCol093 DataCol103 DataCol113 DataCol123?
BlankRow
DataCol016 DataCol026 DataCol036 DataCol046 DataCol056 DataCol066 DataCol076 DataCol086 DataCol096 DataCol106 DataCol116 DataCol126?

<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" span="11"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody>
</tbody>

Markmzz

this look like would almost get me results i am after but i have a number of books this need to be run on and it seems this formular would only work on one boox and add it to new sheet rather than new spreadsheet/workbook.

leewalker · Jul 22, 2013

i have the following code work that will select a set range and copy and paste it to a new workbook

Code:

Sub copyopenpasteclose()
    Dim d As String
    d = Application.ActiveWorkbook.Path
    Range("A1:G1").Copy
    Workbooks.Open Filename:=d & "\copieddata.xlsx"
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

i need help to get it to check line by line rather than at a set range and see if there is a "?" in the last column to copy and past the row it is checking

markmzz · Jul 22, 2013

leewalker said:
this look like would almost get me results i am after but i have a number of books this need to be run on and it seems this formular would only work on one boox and add it to new sheet rather than new spreadsheet/workbook.

Another way (array formula - use Ctrl+Shift+Enter and not only Enter) in a new sheet (Sheet02 for example) of the source wookbook:

In N2 you have the name of the Source Sheet and in M2 you have the Result Range that you have to copy (only values) to the new workbook.

Code:

A2-> =IF(MOD(ROWS(A$2:A2),2),"",IFERROR(INDEX(INDIRECT("'"&$N$2&"'!"&CELL("endereço",A$2)&":"&CELL("endereço",A$4000)),
SMALL(IF(ISNUMBER(SEARCH("~?",INDIRECT("'"&$N$2&"'!"&CELL("endereço",$L$2)&":"&CELL("endereço",$L$4000)))),
ROW($L$2:$L$4000)-ROW($L$2)+1),CEILING(ROWS(A$2:A2),2)/2)),""))

M2-> ="A2:L"&COUNTIF(L:L,"?*")*2-1

The Resut:

Col01

Col02

Col03

Col04

Col05

Col06

Col07

Col08

Col09

Col10

Col11

Col12

*********

Source Sheet

Result Range

Sheet00

A2:L11

DataCol013

DataCol023

DataCol033

DataCol043

DataCol053

DataCol063

DataCol073

DataCol083

DataCol093

DataCol103

DataCol113

DataCol123?

*********

DataCol017

DataCol027

DataCol037

DataCol047

DataCol057

DataCol067

DataCol077

DataCol087

DataCol097

DataCol107

DataCol117

Data?Col127

*********

DataCol0111

DataCol0211

DataCol0311

DataCol0411

DataCol0511

DataCol0611

DataCol0711

DataCol0811

DataCol0911

DataCol1011

DataCol1111

DataCol1211?

*********

DataCol0115

DataCol0215

DataCol0315

DataCol0415

DataCol0515

DataCol0615

DataCol0715

DataCol0815

DataCol0915

DataCol1015

DataCol1115

DataCol1215?

*********

DataCol0118

DataCol0218

DataCol0318

DataCol0418

DataCol0518

DataCol0618

DataCol0718

DataCol0818

DataCol0918

DataCol1018

DataCol1118

DataCol1218?

*********

I tested the new formula with 4000 rows and my computer spent, for each sheet, 60 seconds.

I disabled the Automatic calculation and left in Manual calculation and use Shift + F9 to run the formula in the worksheet.

I hope this help you to solve your problem with only formulas.

Markmzz

leewalker · Jul 24, 2013

hi markmzz

i was really looking for solutions in VBA so i could run it once on multi files in multi folders without to much editing of the documents.

i have the following code

Code:

Sub CopyRows()
    Dim bottomL As Integer
    Dim x As Integer
    bottomL = Sheets("sheet1").Range("L" & Rows.Count).End(xlUp).Row: x = 1
     
    Dim c As Range
    For Each c In Sheets("sheet1").Range("L1:L" & bottomL)
        If c.Value = "?" Then
            c.EntireRow.Copy Worksheets("sheet2").Range("A" & x)
            x = x + 2
        End If
    Next c
     
End Sub

this code copies the data to a new sheet but really i need it to copy to a new workbook for example "copieddata.xls" but having trouble gettin this to work any help would be great.

markmzz · Jul 24, 2013

leewalker said:
hi markmzz

i was really looking for solutions in VBA so i could run it once on multi files in multi folders without to much editing of the documents.

i have the following code

this code copies the data to a new sheet but really i need it to copy to a new workbook for example "copieddata.xls" but having trouble gettin this to work any help would be great.

Try this small modification in your code:

Code:

Sub CopyRows()
    Dim bottomL As Long
    Dim x As Long
    Dim WBO, WBN As Workbook
    Dim mySheet1, mySheet2 As Worksheet
    Dim c As Range    
    Set WBO = ActiveWorkbook
    Set mySheet1 = WBO.Sheets("sheet1")
    Set WBN = Workbooks.Add(xlWBATWorksheet)
    Set mySheet2 = WBN.Worksheets(1)
    mySheet2.Name = "sheetcopieddata"
    bottomL = mySheet1.Range("L" & Rows.Count).End(xlUp).Row
    x = 2
    For Each c In mySheet1.Range("L1:L" & bottomL)
        If InStr(c.Value, "?") Then
            c.EntireRow.Copy mySheet2.Range("A" & x)
            x = x + 2
        End If
    Next c
    WBN.SaveAs "copieddata"
End Sub

Markmzz

rows contains certain text "?" in new document

leewalker

New Member

Excel Facts

shg

MrExcel MVP

leewalker

New Member

shg

MrExcel MVP

markmzz

MrExcel MVP

leewalker

New Member

leewalker

New Member

markmzz

MrExcel MVP

leewalker

New Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

rows contains certain text "?" in new document

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock