Deleting Rows Across Multiple Excel Worksheets 
Author Message
 Deleting Rows Across Multiple Excel Worksheets

Can you please help a newbie?

I need to read down column E of worksheet 3, where I meet a blank cell then
delete the row in all worksheets 1-3.  I have written the following code:

For Each Rw In Worksheets(3).Cells(1, 5).CurrentRegion.Rows ' I don't know
what 1,5 does?
' CurrentRegion starts at A1 and ends at first empty row or column. How do I
get the macro to keep going to end of the spreadsheet?  If  currentregion
not included then macro loops indefinitely?

Contents = Rw.Cells(1, 5).Text ' load contents of cell to variable for debug
If Contents = "" Then Rw.Delete ' delete row if cell empty

Next Rw ' automatically exits from loop at end of data

I am sure the above is total rubbish, but any thoughts anyone?



Mon, 04 Jul 2005 02:12:13 GMT  
 Deleting Rows Across Multiple Excel Worksheets

Davy,

How about this?

    For i = Cells(Rows, counts.End(xlUp).Row, 5) To 1
        If Len(cell.Value) = 0 Then
            For j = 1 To 3
                Worksheets(j).Rows(i).EntireRow.Delete
            Next
        End If
    Next

--
    HTH

    -------

    Bob Phillips


Quote:
> Can you please help a newbie?

> I need to read down column E of worksheet 3, where I meet a blank cell
then
> delete the row in all worksheets 1-3.  I have written the following code:

> For Each Rw In Worksheets(3).Cells(1, 5).CurrentRegion.Rows ' I don't know
> what 1,5 does?
> ' CurrentRegion starts at A1 and ends at first empty row or column. How do
I
> get the macro to keep going to end of the spreadsheet?  If  currentregion
> not included then macro loops indefinitely?

> Contents = Rw.Cells(1, 5).Text ' load contents of cell to variable for
debug
> If Contents = "" Then Rw.Delete ' delete row if cell empty

> Next Rw ' automatically exits from loop at end of data

> I am sure the above is total rubbish, but any thoughts anyone?



Mon, 04 Jul 2005 05:14:13 GMT  
 Deleting Rows Across Multiple Excel Worksheets
Bob,

looks good; but for a novice could you tell me how the first line works?
The bit beginning 'cells'.

thanks

David


Quote:

> Davy,

> How about this?

>     For i = Cells(Rows, counts.End(xlUp).Row, 5) To 1
>         If Len(cell.Value) = 0 Then
>             For j = 1 To 3
>                 Worksheets(j).Rows(i).EntireRow.Delete
>             Next
>         End If
>     Next

> --
>     HTH

>     -------

>     Bob Phillips



> > Can you please help a newbie?

> > I need to read down column E of worksheet 3, where I meet a blank cell
> then
> > delete the row in all worksheets 1-3.  I have written the following
code:

> > For Each Rw In Worksheets(3).Cells(1, 5).CurrentRegion.Rows ' I don't
know
> > what 1,5 does?
> > ' CurrentRegion starts at A1 and ends at first empty row or column. How
do
> I
> > get the macro to keep going to end of the spreadsheet?  If
currentregion
> > not included then macro loops indefinitely?

> > Contents = Rw.Cells(1, 5).Text ' load contents of cell to variable for
> debug
> > If Contents = "" Then Rw.Delete ' delete row if cell empty

> > Next Rw ' automatically exits from loop at end of data

> > I am sure the above is total rubbish, but any thoughts anyone?



Mon, 04 Jul 2005 16:27:54 GMT  
 Deleting Rows Across Multiple Excel Worksheets
Also, the 'cells' in first line generates an error 'wrong number of
arguments or invalid property assignments' in my Excel 2000.

I have just ordered John Green's Excel 2002 VBA Programmers Reference from
Amazon so hopefully will be able to stand on my own two feet soon.

In the meantime, thanks for your help

cheers

Davy


Quote:

> Davy,

> How about this?

>     For i = Cells(Rows, counts.End(xlUp).Row, 5) To 1
>         If Len(cell.Value) = 0 Then
>             For j = 1 To 3
>                 Worksheets(j).Rows(i).EntireRow.Delete
>             Next
>         End If
>     Next

> --
>     HTH

>     -------

>     Bob Phillips



> > Can you please help a newbie?

> > I need to read down column E of worksheet 3, where I meet a blank cell
> then
> > delete the row in all worksheets 1-3.  I have written the following
code:

> > For Each Rw In Worksheets(3).Cells(1, 5).CurrentRegion.Rows ' I don't
know
> > what 1,5 does?
> > ' CurrentRegion starts at A1 and ends at first empty row or column. How
do
> I
> > get the macro to keep going to end of the spreadsheet?  If
currentregion
> > not included then macro loops indefinitely?

> > Contents = Rw.Cells(1, 5).Text ' load contents of cell to variable for
> debug
> > If Contents = "" Then Rw.Delete ' delete row if cell empty

> > Next Rw ' automatically exits from loop at end of data

> > I am sure the above is total rubbish, but any thoughts anyone?



Mon, 04 Jul 2005 16:54:45 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Delete a row in Excel worksheet from VB program

2. Add Row to Excel Worksheet

3. processing excel rows - separate workbooks/worksheets

4. Insert Blank Row Into Excel Worksheet with VB

5. Import multiple excel files with inconsistent number of worksheets in each into ACCESS

6. deleting worksheet in excel workbook

7. Can't delete record from Excel Worksheet

8. VBasic macro to delete worksheet from within Excel

9. Deleting a created Excel worksheet in VB...

10. Worksheet delete using Excel as OLE server

11. Using VB (or VBA) to call code in multiple worksheets of an Excel app

12. Export to Excel - Multiple Worksheets

 

 
Powered by phpBB® Forum Software