Copy the data of a Row based on identifying the data in a cell
Closed
swamyexcel
Posts
1
Registration date
Saturday February 13, 2016
Status
Member
Last seen
February 13, 2016
-
Feb 13, 2016 at 02:55 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Feb 14, 2016 at 12:26 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Feb 14, 2016 at 12:26 AM
Related:
- Copy the data of a Row based on identifying the data in a cell
- Tmobile data check - Guide
- Gta 5 data download for pc - Download - Action and adventure
- Digital data transmission - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Data transmission cable - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Feb 14, 2016 at 12:26 AM
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.
