Macro to search through table and copy all rows with data into another table

**DaBestEva** · 11-03-2020, 09:30 AM

I have a table (table4 on sheet1) that has a bunch of blank rows in it. Due to the rows getting filtered constantly, I can't just hide them as they will reappear every time I press show all. How do I go through a table ranging from A12-Vxxxx and copy every row with visible data into a table on a new sheet (sheet 2)?

**Bernie Deitrick** · 11-03-2020, 10:29 AM

I have no idea what version 1908 means - If you are using 365, take a look at the FILTER function; otherwise, you can create a column of row index formulas like so, entered into cell A1 of the other sheet using Ctrl-Shift-Enter:

=IFERROR(SMALL(IF(Sheet1!$A$1:$A$1000<>"",ROW(Sheet1!$A$1:$A$1000)),ROW()),"")

Copy that down until it returns blanks, and then use INDEX formulas like this in cell B1:

=INDEX(Sheet1!$A$Z,$A1,COLUMN(A1))

Copied down and across to complete the data extract.

Macro to search through table and copy all rows with data into another table

LinkBack

Thread Tools

Rate This Thread

Display

Macro to search through table and copy all rows with data into another table

Re: Macro to search through table and copy all rows with data into another table

Thread Information

Users Browsing this Thread

Similar Threads

Help with table - Copy rows, delete rows, reset table & copy table

Macro to fill 1st table with info from 2nd table. 2nd table search with 2 conditions

Macro to copy rows of data in a table which contains a string and change cell value

macro to copy table from sheet1 to sheet2 (# of rows in table may vary everytime)

Macro to Copy Pivot Table and Paste a Regular Data Table in the Same Position

[SOLVED] Macro to copy data from tables with unspecified rows on multiple sheets into one table

Macro that finds word in table and reports all rows with data to new table

Bookmarks

Bookmarks

Posting Permissions