Urgent!! Delete Entire Rows based on empty cells in a specific column

[VictoriaLJones]

Hi,

I have spent a few hours going through previous threads relating to finding the last used cell and deleting rows based upon this - but due to my limited knowledge, they are either not working or the lack of knowledge means I cant amend them.

Basically what I need to do is:

1. Copy data from 1 cell down (whose reference never changes) along side some existing data (into Column A).
2. This needs to copy down the data for as long as there is data in column B
3. Then select the data from columns A:G, Row - 2:dependent on the above (Thus without the headers).
4. Paste this data into a summary sheet.
5. Do this with the other 20 or so worksheets, and paste append the data below the previous data.

I think it is easy - as have seen other threads which are similar, but as stated above, don’t quite work on my example.

Everything I need to do before and after I have sorted so its just this problem that is holding me up!

Any help would be very much appreciated - am getting kind of desperate now!!!!

Victoria

 

[SkipVought]

Hi Vickie,

Here's some code to do what you want...

Sub CopyStuff()
    Dim wsSummary As Worksheet
    Set wsSummary = Worksheets("Summary")
    For Each Worksheet In ActiveWorkbook.Worksheets
       If Worksheet.Name <> &quot;Summary&quot; Then
          lLastRow = Worksheet.Cells(Worksheet.Cells.Rows.Count, 1).End(xlUp).Row
          If wsSummary.Cells(1, 1).End(xlDown).Row = 65536 Then
            lNextRow = 2
          Else
            lNextRow = wsSummary.Cells(1, 1).End(xlDown).Row + 1
          End If
          Worksheet.Range(Worksheet.Cells(2, &quot;A&quot;), Worksheet.Cells(lLastRow, &quot;G&quot;)).Copy _
            Destination:=wsSummary.Cells(lNextRow, 1)
       End If
    Next
End Sub

However, it looks like you have multiple sheets with similar data -- BAD WORKBOOK DESIGN!!!

Whenever possible, keep ALL the similar data in a single sheet. You might have to add a column for the data that you have in the sheet tab.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VictoriaLJones]

Tell me about it - re the design of the spreadsheets - unfortunately outside my control - comes in from an external company - hence the need for this code to get it into this format!!

Code works a treat though!! thanks v much!

Victoria
xx

 

[xlbo]

Victoria. If Skip helped you (which it looks like he did), you should award him a "star"
You can do this by clicking on the "Mark this post as a helpful / Expert post" link at the bottom of the appropriate thread. This is not only the TT way of saying thanks but it also helps those people who search the archives to look at only those posts with helpful / expert comments / solutions

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes