Copy the data in a row identifying a blank cell in that row

[swamyexcel]

Hi,

In a work sheet I have say 20 columns. In "I" row I have some blank cells (not all).

I want to copy that data from those rows where "I" column has blank cells.

Can any help me, please.

Regards
Swamy

[sourabhg98]

Where do want to paste the data?
In a new sheet?

Edit- Also mention which 20 columns are you talking about.

[swamyexcel]

Yes I want to paste the date in Sheet 2.

I have data in about 20 columns (from A to T); and about 1000 rows.

There are some blank cells in "I" column.

I want to copy entire data from that row where "I" column cells are blank, and paste it in new worksheet.

[sourabhg98]

Please Login or Register  to view this content.

Things to be kept in mind while using this macro-
1. For n = 2 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Macro begins from second row and the last row is calculated on the basis of last used cell in Column A.

2. Sheets("Sheet2").Range("A" & l).Resize(, 20).Value = Sheets("Sheet1").Range("A" & n).Resize(, 20).Value
Macro copies all values from Column A to 20 columns to the right.
Please note that values are copied not moved. So the rows are not deleted from Sheet1.

3. If macro is hit twice then data will be not be overwritten in Sheet2. Instead all the data will again be added in Sheet2.

Check attached for reference.