If data in any cell of the row, copy to the sheet2

**Stiansen** · 09-01-2011, 06:56 AM

Hi,

I have a table ranging from A1:AF3500 where there are data scattered around in different cells, and many of the rows are completely empty. What I want to do is to delete all these rows, or copy all the rows with data to a new sheet (think the last one is the easiest?).

The tricky thing is that I can't look at just one column for data, since the data is all over the range of each row. So i need to search every row to see if there are any data in any of the cells, if yes, then copy to the other sheet.

Been trying macro but I don't have much skills in programming.

Is there anyone that can help me?

**Domski** · 09-01-2011, 07:03 AM

This will delete all the blank rows:

Sub DeleteEmptyRows()
Dim LastRow As Integer
Dim MyRow As Integer
Application.ScreenUpdating = False
   LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count
 For MyRow = LastRow To 1 Step -1
        If Application.CountA(Rows(MyRow)) = 0 Then Rows(MyRow).Delete
    Next MyRow
Application.ScreenUpdating = True
End Sub

Dom

**Stiansen** · 09-01-2011, 08:47 AM

Thank you very much!

Follow up question.
If I have data looking like this:

______A_______B________C
1 _System A
2 __________Equip. 1
3 ____________________Item 1
4 ____________________Item 2
5 __________Equip. 2
6 ____________________Item 1
7_System B

etc.

How can I get "System A" to fill out all the rows in "Column A" (with "System A") down to "System B" (Then System B to System C etc.). And again how to fill "SubEquip." down to next type "SubEquip." etc.

Thank you!

**Domski** · 09-01-2011, 08:50 AM

Can you load a sample workbook showing the layout of the data as it is and the desired result?

Dom

**Stiansen** · 09-02-2011, 02:30 AM

I looked at the workbook again and the your delete code worked perfectly, but the data is scattered all over from A:AF and there is no logic to it so I will instead contact my supplier and tell them to work it out!

But thanks alot for the help, and I'll prolly be back soon with more questions!