Results 1 to 7 of 7

Delete Empty Rows of Table And Optimize Code

Threaded View

snapfade Delete Empty Rows of Table... 12-13-2013, 10:16 PM
GC Excel Re: Delete Empty Rows of... 12-13-2013, 11:19 PM
berlan Re: Delete Empty Rows of... 12-13-2013, 11:56 PM
protonLeah Re: Delete Empty Rows of... 12-14-2013, 12:04 AM
snapfade Re: Delete Empty Rows of... 12-14-2013, 04:06 PM
berlan Re: Delete Empty Rows of... 12-14-2013, 04:12 PM
snapfade Re: Delete Empty Rows of... 12-14-2013, 04:56 PM
  1. #1
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Delete Empty Rows of Table And Optimize Code

    I have four worksheets (see sample). The goal is to copy Col A from the first three worksheets to Col A of the fourth worksheet, sort the result and get rid of empty rows. Here is what I have done so far in the code below:

    Delete old data from Col A of table on Worksheet 4
    Copy Col A of Worksheet 1 to Col A of Worksheet 4
    Copy Col A of Worksheet 2 to Col A of Worksheet 4
    Copy Col A of Worksheet 3 to Col A of Worksheet 4
    Sort Col A of Worksheet 4 to separate data from empty cells

    Sub GetData()
        Dim NumRows As Long
        Dim NextRow As Long
        Dim Rw As Long
        Dim ws1 As Worksheet: Set ws1 = Sheets("One")
        Dim ws2 As Worksheet: Set ws2 = Sheets("Two")
        Dim ws3 As Worksheet: Set ws3 = Sheets("Three")
        Dim ws4 As Worksheet: Set ws4 = Sheets("Four")
        Dim rng1 As Range: Set rng1 = ws1.Range("A2", ws1.Cells(Rows.Count, 1).End(xlUp))
        Dim rng2 As Range: Set rng2 = ws2.Range("A2", ws2.Cells(Rows.Count, 1).End(xlUp))
        Dim rng3 As Range: Set rng3 = ws3.Range("A2", ws3.Cells(Rows.Count, 1).End(xlUp))
        Dim rng4 As Range: Set rng4 = ws4.Range("A2", ws4.Cells(Rows.Count, 1).End(xlUp))
    'Prepoare receiving worksheet by deleting prior content
        ws4.Activate
        NumRows = ActiveSheet.UsedRange.Rows.Count
        Range("A2:A" & NumRows).ClearContents
        Range("A3:G" & NumRows).Delete
    'Copy Column1 of Worksheet One and paste to Column1 of Worksheet Four
        rng1.Copy Destination:=ws4.Range("A2")
    'Find out where the pasted data ends
        With ws4
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With
    'Copy Column1 of Worksheet Two and paste to Column1 of Worksheet Four
        rng2.Copy Destination:=ws4.Range("A" & NextRow)
    'Find out where the pasted data ends
        With ws4
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With
    'Copy Column1 of Worksheet Three and paste to Column1 of Worksheet Four
        rng3.Copy Destination:=ws4.Range("A" & NextRow)
    'Sort the worksheet to force empty rows to the bottom of the table
        ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort.SortFields.Add _
            Key:=Range("tblFour[Column1]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    '--------------------------------------------------------
    'This is where I am stuck.
    'I need to delete the empty rows below the pasted data
    '--------------------------------------------------------
    End Sub
    My original idea was to copy the data into an array or variable, sort the data to exclude the empty cells and then paste the data into Worksheet 4 but I am sorely lacking in the proper skills.

    Would you kindly look this over and suggest a better way?

    Thanks!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code Amendment - Delete Empty Rows
    By TextMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2013, 08:03 AM
  2. [SOLVED] still have empty cell after run Delete empty cells code
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2012, 04:28 PM
  3. Autofilter/delete empty rows is deleting non-empty rows!
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2010, 12:31 PM
  4. Optimize delete dupe rows
    By Engineers2008 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2010, 04:54 PM
  5. Code to Delete Empty Rows in a Word Table
    By Araknia777 via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-09-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1