Make a list from selected rows skipping non selected rows

[paxomar]

I have spread sheet that contains a list of all the shipping documents that we have. I need to make another spread sheet that will copy selected rows only skipping the ones that are not selected. For example I have this:

-    A        B          C            D           E            F             G         
1             Name     Date        Doc#      Driver      facility      truck
2     x       Name     Date        Doc#      Driver      facility      truck
3             Name     Date        Doc#      Driver      facility      truck
4     x       Name     Date        Doc#      Driver      facility      truck
5             Name     Date        Doc#      Driver      facility      truck
6     x       Name     Date        Doc#      Driver      facility      truck
7     x       Name     Date        Doc#      Driver      facility      truck

So I just want to make another sheet copping the rows that have "x" on row A.

I been trying to search for something like this but I can't find a VBA or a formula.

Thank you!

 

[hiker95]

paxomar,

Welcome to the MrExcel forum.

With your raw data in worksheet Sheet1, beginning in cell A1:

Excel 2007
	A	B	C	D	E	F	G
1		Name1	Date	Doc#	Driver	facility	truck
2	x	Name2	Date	Doc#	Driver	facility	truck
3		Name3	Date	Doc#	Driver	facility	truck
4	x	Name4	Date	Doc#	Driver	facility	truck
5		Name5	Date	Doc#	Driver	facility	truck
6	x	Name6	Date	Doc#	Driver	facility	truck
7		Name7	Date	Doc#	Driver	facility	truck
8	x	Name8	Date	Doc#	Driver	facility	truck
9
Sheet1

After the macro in a newly added worksheet:

Excel 2007
	A	B	C	D	E	F	G
1	x	Name2	Date	Doc#	Driver	facility	truck
2	x	Name4	Date	Doc#	Driver	facility	truck
3	x	Name6	Date	Doc#	Driver	facility	truck
4	x	Name8	Date	Doc#	Driver	facility	truck
5
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetX()
' hiker95, 03/18,2013
' http://www.mrexcel.com/forum/excel-questions/692084-make-list-selected-rows-skipping-non-selected-rows.html
Dim a As Variant, b As Variant
Dim i As Long, ii As Long, n As Long
With Sheets("Sheet1")
  a = .Cells(1).CurrentRegion
  n = Application.CountIf(.Columns(1), "x")
  ReDim b(1 To n, 1 To UBound(a, 2))
End With
For i = 1 To UBound(a, 1)
  If a(i, 1) = "x" Then
    ii = ii + 1
    b(ii, 1) = a(i, 1)
    b(ii, 2) = a(i, 2)
    b(ii, 3) = a(i, 3)
    b(ii, 4) = a(i, 4)
    b(ii, 5) = a(i, 5)
    b(ii, 6) = a(i, 6)
    b(ii, 7) = a(i, 7)
  End If
Next i
With Sheets.Add().Cells(1).Resize(n, UBound(b, 2))
  .Value = b
  .Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetX macro.