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)

 

[shina67]

If this doesn't work could you attach a copy of your workbook

 

[Keebler]

Sorry for the delays getting back here, (too many meetings .. not enough sleep )
okay, so i set the workbook up with just the test data (primary source, index and the added index2 sheets)

same result as first tests
created an almost complete mirror of the columns a:d from the INDEX sheet...
(almost because the mirrored sheet deleted the top row when pasting.)

but it copied the blank rows as well as the complete list (in the first 4 columns)

in the tests I have run (out side of here), I have found that the
Cells(sourceWS.Rows.Count, "A") & (sourceRange.Cells(i, J).Value)
only seems to look at the first word in each row. if there is more than one word in the row the command seems to degrade... (just my observation)
but, is that possible, or am i missing something more?

 

[Keebler]

sure,
here is a much sanitized version (the actual WB is well over 20mb, compared with the sheets that are actually needed for this...) this is only 45kb

okay, so i guess i do not know how to upload the WB, I can not find a place to upload ANYTHING other than a image... (MODS???? any help here?)

 

[Keebler]

so, there is no current way to upload the workbook

 

[shina67]

If you use the XL2BB tool that is on one of the pinned posts it will allow you to upload your WB

 

[shina67]

You need to use XL2BB tool that is in a pinned post on the forum to upload your WB.

Can you check if there are any formatting issues or unexpected characters in the source range that might affect the copying process.

 

[Alex Blakenburg]

The code @shina67 has provided you with works for me,
How is your data getting into your spreadsheet ?
Do you have any formulas in those cells ? And what do those formulas look like, to they return "" or something similar ?

If you are not using a formula then paste the below into a new sheet.
Click on the copy icon of the below and paste it into A1
Copy A:D of a row that should be disappearing and is not into the coloured (input) line.
Show us a picture of the resulting screen (include the column headings ABCD in your image)

20240306 VBA Delete Rows Keebler.xlsm
	A	B	C	D	E
1					<--- Paste in Blank row from workbook
2	0	1	1	0	<--- Length
3	#VALUE!	160	8203	#VALUE!	<--- 1st Character
Is it blank

Cell Formulas
Range	Formula
A1	A1	=""
A2:D2	A2	=LEN(A1)
A3:D3	A3	=UNICODE(LEFT(A1,1))

If you do see a special character and want to do a Test.
Make the activesheet INDEX and run the code below inserting whatever character you saw where I have the 8203
Then run Shina's code again

Sub RemoveChar()
    ActiveSheet.UsedRange.Replace What:=ChrW(8203), Replacement:="", LookAt:=xlPart
End Sub

 

[Keebler]

aLEX b,
the data that is being imported into the INDEX page (where I am trying to remove the blank rows) by a VBA routine. further, the data is formatted in the source page(s) (using a whole bunch of formulas).

so, the steps are
data is produced from an external source (usually in the form of a txt file)
the data is stripped from the txt file and pasted into a blank sheet (pre-loaded with lots of formulas)
the data is then formatted (to the specs that are desired by the existing formulas on each sheet)
the formatted data is then selected and copied (values only, so no formulas) using VBA to the INDEX page
then on the INDEX page is where I want to sort, remove all the blank rows, etc

i dont have any issue with having a second INDEX2 page where the end result produces the desired results..
I have created a sandbox version of the WB to do all the testing so not to destroy the original data set (of course)
(i have learned from experience that lesson the hard way)

as for the VBA provided by SHINA67. the VBA is quite insightful and i am very grateful. I have tried numerous iterations of the VBA to get the desired result (both from here and on my own). the help i have received here has taught me so much, that i refer others here as one of the best sources for vba help (not just with excel but access as well)
to date,
the VBA works as expected with only one word in the column, but using any kind of full length string (datadata - datadata - datadata blah blah blah) seems to mess everything up. it seems that the VBA does not know how to except a string with spaces or other types of sentences. the VBA provided works just great with any cell no matter how long (i tried one with 75 characters, no spaces or other digits) and it worked just great... BUT, the VBA fails when i use the imported//copied (from another sheet).
i should also note that the data being imported//copied has 3 columns that are spaced apart by 16 columns... (the index page has text on the top row across the sheet, but this is easily enough copied over again) BUT, for these tests i only have been importing//copying the first column.
i constantly get the same error that no blank cells found (or something similar), which has sparked me asking many times if it only works with one word cells...

in all the tests that are successful. they only have one word (of any length, no spaces or punctuation marks) in them. anything else, fails.
using rows with "Supercalifragilisticexpialidocious" work as expected
using rows with "Super cali frag i listic expi ali docious" fail, as do
using rows with "Super-cali-frag-i-listic-expi-ali-docious" fail, as do
using rows with "Super - cali - frag - i - listic - expi - ali - docious" fail
i do not understand why this occurs

 

[Keebler]

shina67,

here is the first index sheet

FLAC MASTERS 3 1 555b320(SANDTRAP_b).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SORT:ARTIST		AR TI AB TK LO		124	-1					LAST UPDATE
2
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
23
24	ABBA - SUMMER NIGHT CITY - 100% ABBA {DISC 2} 2019 - 01 - FMA0101 [ALBUMS]
25	ABBA - HEAD OVER HEELS - 100% ABBA {DISC 2} 2019 - 02 - FMA0101 [ALBUMS]
26	ABBA - EAGLE (SHORT VERSION) - 100% ABBA {DISC 2} 2019 - 03 - FMA0101 [ALBUMS]
27	ABBA - THE VISITORS - 100% ABBA {DISC 2} 2019 - 04 - FMA0101 [ALBUMS]
28	ABBA - ON AND ON AND ON - 100% ABBA {DISC 2} 2019 - 06 - FMA0101 [ALBUMS]
29	ABBA - I WONDER (DEPARTURE) - 100% ABBA {DISC 2} 2019 - 09 - FMA0101 [ALBUMS]
30	ABBA - LOVELIGHT {ORIGINAL VERSION} - 100% ABBA {DISC 2} 2019 - 11 - FMA0101 [ALBUMS]
31	ABBA - CASSANDRA - 100% ABBA {DISC 2} 2019 - 12 - FMA0101 [ALBUMS]
32	ABBA - UNDER ATTACK - 100% ABBA {DISC 2} 2019 - 13 - FMA0101 [ALBUMS]
33	ABBA - SLIPPING THROUGH MY FINGERS - 100% ABBA {DISC 2} 2019 - 14 - FMA0101 [ALBUMS]
34	ABBA - I'VE BEEN WAITING FOR YOU - 100% ABBA {DISC 2} 2019 - 15 - FMA0101 [ALBUMS]
35	ABBA - GONNA SING YOU MY LOVE SONG - 100% ABBA {DISC 2} 2019 - 16 - FMA0101 [ALBUMS]
36	ABBA - I AM THE CITY - 100% ABBA {DISC 2} 2019 - 17 - FMA0101 [ALBUMS]
37	ABBA - GRACIAS POR LA MUSICA {SPANISH} - 100% ABBA {DISC 2} 2019 - 19 - FMA0101 [ALBUMS]
38	ABBA - THAT'S ME - 100% ABBA {DISC 2} 2019 - 21 - FMA0101 [ALBUMS]
39	ABBA - HAPPY NEW YEAR - 100% ABBA {DISC 2} 2019 - 22 - FMA0101 [ALBUMS]
40	ABBA - SOLDIERS - 100% ABBA {DISC 2} 2019 - 23 - FMA0101 [ALBUMS]
41	ABBA - CHIQUITITA {SPANISH VERSION} - 100% ABBA {DISC 2} 2019 - 24 - FMA0101 [ALBUMS]
42
43
44	ABBA - I STILL HAVE FAITH IN YOU - VOYAGE 2021 - 01 - FMA0101 [ALBUMS]
45	ABBA - LITTLE THINGS - VOYAGE 2021 - 03 - FMA0101 [ALBUMS]
46	ABBA - DON'T SHUT ME DOWN - VOYAGE 2021 - 04 - FMA0101 [ALBUMS]
47	ABBA - JUST A NOTION - VOYAGE 2021 - 05 - FMA0101 [ALBUMS]
48	ABBA - I CAN BE THAT WOMAN - VOYAGE 2021 - 06 - FMA0101 [ALBUMS]
49	ABBA - KEEP AN EYE ON DAN - VOYAGE 2021 - 07 - FMA0101 [ALBUMS]
50	ABBA - BUMBLEBEE - VOYAGE 2021 - 08 - FMA0101 [ALBUMS]
51	ABBA - ODE TO FREEDOM - VOYAGE 2021 - 10 - FMA0101 [ALBUMS]
52
53
54
55
56	ACE OF BASE - WONDERFUL LIFE - DA CAPO [JAPAN PROMO] 2003 - 07 - FMA0101 [ALBUMS]
57
58
59	ACE OF BASE - TRAVEL TO ROMANTIS - FLOWERS 1998 - 04 - FMA0101 [ALBUMS]
60	ACE OF BASE - DONNIE - FLOWERS 1998 - 13 - FMA0101 [ALBUMS]
61	ACE OF BASE - CRUEL SUMMER {BONUS MIX} - FLOWERS 1998 - 14 - FMA0101 [ALBUMS]
62
63
64	ACE OF BASE - EVERYTIME IT RAINS - GREATEST HITS 2000 - 03 - FMA0101 [ALBUMS]
65	ACE OF BASE - BEAUTIFUL LIFE {JUNIOR VASQUEZ MIX} - GREATEST HITS 2000 - 12 - FMA0101 [ALBUMS]
66
67
68	ACE OF BASE - CRUEL SUMMER {BONUS MIX} - GREATEST HITS 2008 - 12 - FMA0101 [ALBUMS]
69
70
71	ACE OF BASE - EVERYTIME IT RAINS - PLAYLIST THE VERY BEST OF ACE OF BASE 2011 - 09 - FMA0101 [ALBUMS]
72	ACE OF BASE - ALWAYS HAVE, ALWAYS WILL - PLAYLIST THE VERY BEST OF ACE OF BASE 2011 - 12 - FMA0101 [ALBUMS]
73
74
75	ACE OF BASE - LOVE IN DECEMBER - SINGLES OF THE 90S 1999 - 06 - FMA0101 [ALBUMS]
76	ACE OF BASE - CRUEL SUMMER - SINGLES OF THE 90S 1999 - 11 - FMA0101 [ALBUMS]
INDEXA

Cell Formulas
Range	Formula
A1	A1	=IF('D:\Downloads\[FLAC MASTERS 3 1 555b306.xlsm]DATA'!$AH$3="","",'D:\Downloads\[FLAC MASTERS 3 1 555b306.xlsm]DATA'!$AH$3)
C1	C1	=IF(A1="","",HLOOKUP(A1,'D:\Downloads\[FLAC MASTERS 3 1 555b306.xlsm]DATA'!$AH$3:$AJ$5,3,FALSE))
E1	E1	=MAX((A:A<>"")*(ROW(A:A)))
F1	F1	=MAX((B:B<>"")*(ROW(B:B)))-1

as well as the result sheet

FLAC MASTERS 3 1 555b320(SANDTRAP_b).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
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
23
24	ABBA - SUMMER NIGHT CITY - 100% ABBA {DISC 2} 2019 - 01 - FMA0101 [ALBUMS]
25	ABBA - HEAD OVER HEELS - 100% ABBA {DISC 2} 2019 - 02 - FMA0101 [ALBUMS]
26	ABBA - EAGLE (SHORT VERSION) - 100% ABBA {DISC 2} 2019 - 03 - FMA0101 [ALBUMS]
27	ABBA - THE VISITORS - 100% ABBA {DISC 2} 2019 - 04 - FMA0101 [ALBUMS]
28	ABBA - ON AND ON AND ON - 100% ABBA {DISC 2} 2019 - 06 - FMA0101 [ALBUMS]
29	ABBA - I WONDER (DEPARTURE) - 100% ABBA {DISC 2} 2019 - 09 - FMA0101 [ALBUMS]
30	ABBA - LOVELIGHT {ORIGINAL VERSION} - 100% ABBA {DISC 2} 2019 - 11 - FMA0101 [ALBUMS]
31	ABBA - CASSANDRA - 100% ABBA {DISC 2} 2019 - 12 - FMA0101 [ALBUMS]
32	ABBA - UNDER ATTACK - 100% ABBA {DISC 2} 2019 - 13 - FMA0101 [ALBUMS]
33	ABBA - SLIPPING THROUGH MY FINGERS - 100% ABBA {DISC 2} 2019 - 14 - FMA0101 [ALBUMS]
34	ABBA - I'VE BEEN WAITING FOR YOU - 100% ABBA {DISC 2} 2019 - 15 - FMA0101 [ALBUMS]
35	ABBA - GONNA SING YOU MY LOVE SONG - 100% ABBA {DISC 2} 2019 - 16 - FMA0101 [ALBUMS]
36	ABBA - I AM THE CITY - 100% ABBA {DISC 2} 2019 - 17 - FMA0101 [ALBUMS]
37	ABBA - GRACIAS POR LA MUSICA {SPANISH} - 100% ABBA {DISC 2} 2019 - 19 - FMA0101 [ALBUMS]
38	ABBA - THAT'S ME - 100% ABBA {DISC 2} 2019 - 21 - FMA0101 [ALBUMS]
39	ABBA - HAPPY NEW YEAR - 100% ABBA {DISC 2} 2019 - 22 - FMA0101 [ALBUMS]
40	ABBA - SOLDIERS - 100% ABBA {DISC 2} 2019 - 23 - FMA0101 [ALBUMS]
41	ABBA - CHIQUITITA {SPANISH VERSION} - 100% ABBA {DISC 2} 2019 - 24 - FMA0101 [ALBUMS]
42
43
44	ABBA - I STILL HAVE FAITH IN YOU - VOYAGE 2021 - 01 - FMA0101 [ALBUMS]
45	ABBA - LITTLE THINGS - VOYAGE 2021 - 03 - FMA0101 [ALBUMS]
46	ABBA - DON'T SHUT ME DOWN - VOYAGE 2021 - 04 - FMA0101 [ALBUMS]
47	ABBA - JUST A NOTION - VOYAGE 2021 - 05 - FMA0101 [ALBUMS]
48	ABBA - I CAN BE THAT WOMAN - VOYAGE 2021 - 06 - FMA0101 [ALBUMS]
49	ABBA - KEEP AN EYE ON DAN - VOYAGE 2021 - 07 - FMA0101 [ALBUMS]
50	ABBA - BUMBLEBEE - VOYAGE 2021 - 08 - FMA0101 [ALBUMS]
51	ABBA - ODE TO FREEDOM - VOYAGE 2021 - 10 - FMA0101 [ALBUMS]
52
53
54
55
56	ACE OF BASE - WONDERFUL LIFE - DA CAPO [JAPAN PROMO] 2003 - 07 - FMA0101 [ALBUMS]
57
58
59	ACE OF BASE - TRAVEL TO ROMANTIS - FLOWERS 1998 - 04 - FMA0101 [ALBUMS]
60	ACE OF BASE - DONNIE - FLOWERS 1998 - 13 - FMA0101 [ALBUMS]
61	ACE OF BASE - CRUEL SUMMER {BONUS MIX} - FLOWERS 1998 - 14 - FMA0101 [ALBUMS]
62
63
64	ACE OF BASE - EVERYTIME IT RAINS - GREATEST HITS 2000 - 03 - FMA0101 [ALBUMS]
65	ACE OF BASE - BEAUTIFUL LIFE {JUNIOR VASQUEZ MIX} - GREATEST HITS 2000 - 12 - FMA0101 [ALBUMS]
66
67
68	ACE OF BASE - CRUEL SUMMER {BONUS MIX} - GREATEST HITS 2008 - 12 - FMA0101 [ALBUMS]
69
70
71	ACE OF BASE - EVERYTIME IT RAINS - PLAYLIST THE VERY BEST OF ACE OF BASE 2011 - 09 - FMA0101 [ALBUMS]
72	ACE OF BASE - ALWAYS HAVE, ALWAYS WILL - PLAYLIST THE VERY BEST OF ACE OF BASE 2011 - 12 - FMA0101 [ALBUMS]
73
74
75	ACE OF BASE - LOVE IN DECEMBER - SINGLES OF THE 90S 1999 - 06 - FMA0101 [ALBUMS]
76	ACE OF BASE - CRUEL SUMMER - SINGLES OF THE 90S 1999 - 11 - FMA0101 [ALBUMS]
INDEX2

please note that the result is a mirror of INDEX on INDEX2,

 

[Keebler]

i have tried other routines to get the desired results, such as sorting each column manually then removing the spaces

steps
manually sort alphabetically (produces empty rows either at the top or the bottom)
then a seperate routine that removes the blank rows and places filtered in a new column (this is the routine i mentioned at the begining that took hours to complete)
then deleting the data from the first column and moving the data from the sorted, filtered data back to the original column

to do each column (as i mentioned there are three, spaced with 16 blank columnns) would take a week or more (potentially)
then if i ever added more data... the whole process would need to be repeated. (argh)

i know there has to be a better and faster way to do this