new project: copy range without empty or blank rows

Keebler

Keebler

Board Regular
Joined
Dec 1, 2021
Messages
167
Office Version
  1. 2021
Platform
  1. Windows
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.

VBA Code: 
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)
 
Sort by date Sort by votes
Does it make any diffference if you swap out the first line for the second line below:

VBA Code: 
            'If Not IsEmpty(sourceRange.Cells(i, j).Value) Then
            If sourceRange.Cells(i, j).Value <> "" Then
 
Upvote 1

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Alex Blakenburg said:
Does it make any diffference if you swap out the first line for the second line below:

VBA Code: 
            'If Not IsEmpty(sourceRange.Cells(i, j).Value) Then
            If sourceRange.Cells(i, j).Value <> "" Then
Click to expand...
forgive me..
what first line?

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

Rich (BB code): 
        ' If destinationLastRow > 0 Then
        If destinationLastRow > 1 Then
 
Upvote 0
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?
 
Upvote 0
how is the boolean used?

and is union like a nesting variable that stores the data in temporarily?
 
Upvote 0
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)
 
Upvote 0
of course, now the obvious question..

will this work with all three columns present?

it does!!!!!!!

yeah!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Keebler
  • Solved
clean code
5 6 7
Replies
66
Views
1K
Keebler
Keebler
D
  • Question
Pasting string - variable in cell - ERROR
Replies
9
Views
272
Alex Blakenburg
A
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
139
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
216
LazyBug
LazyBug
D
  • Solved
Comparing Lists for new values - Dictionary or Array?
2
Replies
10
Views
1K
Davavo
D

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top