Copy the data of a Row based on identifying the data in a cell
Closed
swamyexcel
vcoolio
- Posts
- 1
- Registration date
- Saturday February 13, 2016
- Status
- Member
- Last seen
- February 13, 2016
vcoolio
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Related:
- Copy the data of a Row based on identifying the data in a cell
- Excel Macro to insert row based on data ✓ - Forum - Excel
- Excel create new rows based on column data - Forum - Excel
- Excel repeat rows based on cell value - Guide
- Excel duplicate rows based on cell value ✓ - Forum - Excel
- Excel copy row based on cell value ✓ - Forum - Excel
1 reply
vcoolio
Feb 14, 2016 at 12:26 AM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Feb 14, 2016 at 12:26 AM
Hello SwamyExcel,
The following VBA code should do the task for you:-
The code searches Column I for any blank cells and then transfers the entire row of data associated with the blank cells to Sheet 2.
I'm not sure if you want the "used" data in Sheet 1 cleared once the data has been transferred to Sheet 2. If you do, just remove the apostrophe from the front of the line of code coloured green above.
Following is the link to my test work book for you to peruse. Click on the transfer button to see it work:-
https://www.dropbox.com/s/nzubzfd59pf62e1/SwamyExcel%28Auto%20Filter%20on%20blank%20cells%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
The following VBA code should do the task for you:-
Sub MoveIt() Application.ScreenUpdating = False With ActiveSheet .AutoFilterMode = False With Range("I2", Range("I" & Rows.Count).End(xlUp)) .AutoFilter 1, "" .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1) '.Offset(1).EntireRow.Delete End With .AutoFilterMode = False End With Sheet2.Columns("I").Hidden = True Application.ScreenUpdating = True Sheet2.Select End Sub
The code searches Column I for any blank cells and then transfers the entire row of data associated with the blank cells to Sheet 2.
I'm not sure if you want the "used" data in Sheet 1 cleared once the data has been transferred to Sheet 2. If you do, just remove the apostrophe from the front of the line of code coloured green above.
Following is the link to my test work book for you to peruse. Click on the transfer button to see it work:-
https://www.dropbox.com/s/nzubzfd59pf62e1/SwamyExcel%28Auto%20Filter%20on%20blank%20cells%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.