Copy the data of a Row based on identifying the data in a cell
Closed
swamyexcel
Posts
1
Registration date
Saturday 13 February 2016
Status
Member
Last seen
13 February 2016
-
13 Feb 2016 à 14:55
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 14 Feb 2016 à 00:26
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 14 Feb 2016 à 00:26
Related:
- Based on the value in cells b77
- Based on the values in cells b77 - Excel Forum
- Based on the values in cells b77 b81 what function can automatically return the value in cell c77 ✓ - Excel Forum
- How to change account based in instagram - Instagram Forum
- Based on the values in cells b77 b81 c77 - Excel Forum
- Based on the values in cells b77:b81, which function can automatically return the value in cell c77? ✓ - Excel Forum
1 response
vcoolio
Posts
1411
Registration date
Thursday 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
14 Feb 2016 à 00:26
14 Feb 2016 à 00:26
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.
