Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copy the data of a Row based on identifying the data in a cell [Closed]

Report
Posts
1
Registration date
Saturday February 13, 2016
Status
Member
Last seen
February 13, 2016
 -
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
Hello,

I have data in about 20 columns and 100 rows

There are some blank cells in column "I" (about 20 blank cells totally in "I" column, at different rows)

I want to copy data from those rows where cells in "I" column are blank.

Can any one help me?

Regards
See more

1 reply

Answer 1 / 1
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
Hello SwamyExcel,

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.
Ask a question