endxlup failure because of table?

humboldtcountyguy

New Member
Joined
May 21, 2010
Messages
20
Can anyone tell me why the code below copies an entire table of blank rows instead of only those with data? And more importantly, how do i fix it? Thanks in advance guys :)

Code:
Sub ConsolidateCSV()

Dim sh As Worksheet, lr As Long, rng As Range
Dim si As Worksheet

Set sh = WorkSheets("Tables")
Set si = Worksheets("CSV")

lr = sh.Cells(Rows.Count, "A").End(xlUp).Row 'This finds the last row in the range
sh.Range("A2:E" & lr).Copy
Set pst = si.Range("a5535").End(xlUp).Offset(1, 0)
pst.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try this one. Minor variations but might be the key.

Code:
Sub ConsolidateCSV()

Dim sh As Worksheet, lr As Long, rng As Range
Dim si As Worksheet
Dim pst As Range


Set sh = Worksheets("Tables")
Set si = Worksheets("CSV")


lr = sh.Cells(Rows.Count, 1).End(xlUp).Row 'This finds the last row in the range
sh.Range("A2:E" & lr).Copy
Set pst = si.Cells(Rows.Count, 1).End(xlUp).Offset(1)
pst.PasteSpecial


End Sub

Let us know if this helps. :)
 
Upvote 0
Could be because that cell was edited before, whether a format or a border was assigned to it.

Replace the Set pst line above with this instead:
Code:
Set pst = si.Cells(1,1).End(xlDown).Offset(1)

Let me know if this helps.
 
Upvote 0
Code:
Sub ConsolidateCSV()
Dim sh As Worksheet, lr As Long, rng As Range
Dim si As Worksheet
Dim pst As Range
Set sh = Worksheets("Tables")
Set si = Worksheets("CSV")
Application.ScreenUpdating = False
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row 'This finds the last row in the range
sh.Range("A2:E" & lr).Copy Destination:=si.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi JMonty, that didn't work either, it is still copying the entire table of blank rows....could it be because the rows contain formulas? every cell of the table has a formula....but i used iferror and isblank to keep the cells printable....wonder if that is my problem? thanks again for the help :)
 
Upvote 0
lancer, thanks but that also copied the entire table of blank rows. also im not sure how to edit that to paste values....im starting to think the formulas are the reason all the rows are being copied, can that be avoided?
 
Upvote 0
Try again:
Code:
Sub ConsolidateCSV()
Dim sh As Worksheet, lr As Long, lr2 As Long
Dim si As Worksheet, i As Long
Set sh = Worksheets("Tables")
Set si = Worksheets("CSV")
Application.ScreenUpdating = False
lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = si.Cells(Rows.Count, "A").End(xlUp).Row
sh.Activate
For i = 2 To lr
    If Cells(i, 1).Value <> "" Then
        lr2 = lr2 + 1
        sh.Cells(i, 1).EntireRow.Copy Destination:=si.Cells(lr2, 1)
    End If
Next
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
If that's the case then the following might work...

Code:
pst.PasteSpecial Paste:=xlPasteValues

Give it a go and let us know. :)
 
Upvote 0

Forum statistics

Threads
1,212,101
Messages
6,105,962
Members
447,987
Latest member
rasika_99

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