new project: copy range without empty or blank rows

[Keebler]

I am currently working on a project to copy a range from one worksheet to another removing empty or blank cells (rows) and pasting them into another ws at the bottom of the page.

Sub copyto_test()
'define variables
Dim lrow As Long, srow As Long, erow As Long, crow As Long
Dim slist As String, srng As String, trng As String
Dim aws As Worksheet, sws As Worksheet, tws As Worksheet
Dim crg As Range

'set constants
Set aws = activesheet
Set tws = Sheets("INDEX")

lrow = tws.Range("e1") 'gets the last row of destination ws
If lrow <= 3 Then 'checks to make sure row is at least row 3
trng = tws.Range("a3").Address
Else
trng = tws.Range("a" & (lrow + 1))
End If
crow = aws.Range("e1") 'gets the last row of the current sheet
srow = aws.Range("h1") 'gets the first row of the current sheet
srng = Range("aa" & crow)
slist = ("k" & srow & ":" & srng)


Range(slist).Copy Range(trng).PasteSpecial(xlPasteValues)




End Sub

so the problem is the last line.

Range(slist).Copy Range(trng).PasteSpecial(xlPasteValues)

unable to get the pastespecial property of the range class

im sure it is something stupid im missing...

no other errors are showing up --- at this time (and no i havent tried the removal of the blank rows yet)

 

[Alex Blakenburg]

Does it make any diffference if you swap out the first line for the second line below:

            'If Not IsEmpty(sourceRange.Cells(i, j).Value) Then
            If sourceRange.Cells(i, j).Value <> "" Then

 

[Keebler]

Does it make any diffference if you swap out the first line for the second line below:

            'If Not IsEmpty(sourceRange.Cells(i, j).Value) Then
            If sourceRange.Cells(i, j).Value <> "" Then

forgive me..
what first line?

Sub copyto_test_REMOVEBLANKS_b15()

    Dim sourceWS As Worksheet
    Dim destinationWS As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim lastRow As Long
    Dim i As Long, J As Long
    Dim destinationLastRow As Long
    Dim emptyRow As Boolean
   
    Set sourceWS = ThisWorkbook.Sheets("INDEX")
    Set destinationWS = ThisWorkbook.Sheets("INDEX2")
   
    lastRow = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).row
   
     Set sourceRange = sourceWS.Range("A1:D" & lastRow)
   
    destinationWS.Cells.Clear

    For i = 1 To sourceRange.Rows.Count
        emptyRow = True
        For J = 1 To sourceRange.Columns.Count
            If Not IsEmpty(sourceRange.Cells(i, J).Value) Then
                emptyRow = False
                Exit For
            End If
        Next J
        If Not emptyRow Then
            If Not destinationRange Is Nothing Then
                Set destinationRange = Union(destinationRange, sourceRange.Rows(i))
            Else
                Set destinationRange = sourceRange.Rows(i)
            End If
        End If
    Next i
   
    destinationLastRow = destinationWS.Cells(destinationWS.Rows.Count, "A").End(xlUp).row
 
    If Not destinationRange Is Nothing Then
        If destinationLastRow > 0 Then
            destinationRange.Copy destinationWS.Cells(destinationLastRow + 1, "A")
        Else
            destinationRange.Copy destinationWS.Range("A1")
        End If
    End If
End Sub

 

[Keebler]

BINGO

 

[Keebler]

Sub copyto_test_REMOVEBLANKS_b15()

    Dim sourceWS As Worksheet
    Dim destinationWS As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim lastRow As Long
    Dim i As Long, J As Long
    Dim destinationLastRow As Long
    Dim emptyRow As Boolean
  
    Set sourceWS = ThisWorkbook.Sheets("INDEX")
    Set destinationWS = ThisWorkbook.Sheets("INDEX2")
  
    lastRow = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).row
  
     Set sourceRange = sourceWS.Range("A1:D" & lastRow)
  
    destinationWS.Cells.Clear

    For i = 1 To sourceRange.Rows.Count
        emptyRow = True
        For J = 1 To sourceRange.Columns.Count
            If sourceRange.Cells(i, J).Value <> "" Then
                emptyRow = False
                Exit For
            End If
        Next J
        If Not emptyRow Then
            If Not destinationRange Is Nothing Then
                Set destinationRange = Union(destinationRange, sourceRange.Rows(i))
            Else
                Set destinationRange = sourceRange.Rows(i)
            End If
        End If
    Next i
  
    destinationLastRow = destinationWS.Cells(destinationWS.Rows.Count, "A").End(xlUp).row
 
    If Not destinationRange Is Nothing Then
        If destinationLastRow > 0 Then
            destinationRange.Copy destinationWS.Cells(destinationLastRow + 1, "A")
        Else
            destinationRange.Copy destinationWS.Range("A1")
        End If
    End If
End Sub

alexb,
you were right!! practically perfect result

 

[Keebler]

practically perfect
meaning that it copied the top row from the index page to INDEX2 placing it on row 2

how does the filtering work? and is there a way to have this filter start on row 3, sort and place the filtered data starting on row 3

FLAC MASTERS 3 1 555b320(SANDTRAP_b).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	SORT:ARTIST		AR TI AB TK LO
3	ABBA - DANCING QUEEN - 100% ABBA {DISC 1} 2019 - 01 - FMA0101 [ALBUMS]
4	ABBA - GIMME! GIMME! GIMME! (A MAN AFTER MIDNIGHT) - 100% ABBA {DISC 1} 2019 - 03 - FMA0101 [ALBUMS]
5	ABBA - MAMMA MIA - 100% ABBA {DISC 1} 2019 - 05 - FMA0101 [ALBUMS]
6	ABBA - THE WINNER TAKES IT ALL - 100% ABBA {DISC 1} 2019 - 06 - FMA0101 [ALBUMS]
7	ABBA - TAKE A CHANCE ON ME - 100% ABBA {DISC 1} 2019 - 07 - FMA0101 [ALBUMS]
8	ABBA - KNOWING ME, KNOWING YOU - 100% ABBA {DISC 1} 2019 - 08 - FMA0101 [ALBUMS]
9	ABBA - VOULEZ-VOUS - 100% ABBA {DISC 1} 2019 - 09 - FMA0101 [ALBUMS]
10	ABBA - LAY ALL YOUR LOVE ON ME - 100% ABBA {DISC 1} 2019 - 11 - FMA0101 [ALBUMS]
11	ABBA - THANK YOU FOR THE MUSIC - 100% ABBA {DISC 1} 2019 - 12 - FMA0101 [ALBUMS]
12	ABBA - SUPER TROUPER - 100% ABBA {DISC 1} 2019 - 13 - FMA0101 [ALBUMS]
13	ABBA - ONE OF US - 100% ABBA {DISC 1} 2019 - 14 - FMA0101 [ALBUMS]
14	ABBA - AS GOOD AS NEW - 100% ABBA {DISC 1} 2019 - 15 - FMA0101 [ALBUMS]
15	ABBA - FERNANDO - 100% ABBA {DISC 1} 2019 - 16 - FMA0101 [ALBUMS]
16	ABBA - THE NAME OF THE GAME - 100% ABBA {DISC 1} 2019 - 17 - FMA0101 [ALBUMS]
17	ABBA - CHIQUITITA - 100% ABBA {DISC 1} 2019 - 18 - FMA0101 [ALBUMS]
18	ABBA - I HAVE A DREAM - 100% ABBA {DISC 1} 2019 - 19 - FMA0101 [ALBUMS]
19	ABBA - OUR LAST SUMMER - 100% ABBA {DISC 1} 2019 - 23 - FMA0101 [ALBUMS]
20	ABBA - THE DAY BEFORE YOU CAME - 100% ABBA {DISC 1} 2019 - 24 - FMA0101 [ALBUMS]
21	ABBA - ANGEL EYES - 100% ABBA {DISC 1} 2019 - 25 - FMA0101 [ALBUMS]
22	ABBA - SUMMER NIGHT CITY - 100% ABBA {DISC 2} 2019 - 01 - FMA0101 [ALBUMS]
23	ABBA - HEAD OVER HEELS - 100% ABBA {DISC 2} 2019 - 02 - FMA0101 [ALBUMS]
24	ABBA - EAGLE (SHORT VERSION) - 100% ABBA {DISC 2} 2019 - 03 - FMA0101 [ALBUMS]
25	ABBA - THE VISITORS - 100% ABBA {DISC 2} 2019 - 04 - FMA0101 [ALBUMS]
26	ABBA - ON AND ON AND ON - 100% ABBA {DISC 2} 2019 - 06 - FMA0101 [ALBUMS]
27	ABBA - I WONDER (DEPARTURE) - 100% ABBA {DISC 2} 2019 - 09 - FMA0101 [ALBUMS]
28	ABBA - LOVELIGHT {ORIGINAL VERSION} - 100% ABBA {DISC 2} 2019 - 11 - FMA0101 [ALBUMS]
29	ABBA - CASSANDRA - 100% ABBA {DISC 2} 2019 - 12 - FMA0101 [ALBUMS]
30	ABBA - UNDER ATTACK - 100% ABBA {DISC 2} 2019 - 13 - FMA0101 [ALBUMS]
31	ABBA - SLIPPING THROUGH MY FINGERS - 100% ABBA {DISC 2} 2019 - 14 - FMA0101 [ALBUMS]
32	ABBA - I'VE BEEN WAITING FOR YOU - 100% ABBA {DISC 2} 2019 - 15 - FMA0101 [ALBUMS]
33	ABBA - GONNA SING YOU MY LOVE SONG - 100% ABBA {DISC 2} 2019 - 16 - FMA0101 [ALBUMS]
INDEX2

 

[Alex Blakenburg]

A formula that returns "" is not Empty.
However an = "" test will pick up BOTH "" and Empty

In terms of the output try changing the 0 to a 1 in this line

        ' If destinationLastRow > 0 Then
        If destinationLastRow > 1 Then

 

[Keebler]

i want to understand not just that the VBA works, but how and why it works

alexb, the line you added
If sourceRange.Cells(i, J).Value <> "" Then

sourcerange is the range to be evaluated
cells(i,j) is the address i is the row, j is the column
the <> "" is if the address does not equal ""

am i correct?

 

[Keebler]

how is the boolean used?

and is union like a nesting variable that stores the data in temporarily?

 

[Alex Blakenburg]

Your post #57 is correct although on a technicality it is not the address that is "" but the value stored in that address.

The boolean emptyrow is used to determine whether you want the row in the output or not with emptyrow = False being the ones you do want.
The ones you do want are being effectively concatenated or collected using the Union function. It doesn't store the data it collects the Range addresses (a pointer to those ranges)

 

[Keebler]

of course, now the obvious question..

will this work with all three columns present?

it does!!!!!!!

yeah!