endxlup failure because of table?

[humboldtcountyguy]

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

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

 

[JMonty]

Try this one. Minor variations but might be the key.

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.

 

[humboldtcountyguy]

Hi JMonty, thanks for the response, unfortunately that code pasted my data in row 1034720.....any idea why that would be?

 

[JMonty]

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:

Set pst = si.Cells(1,1).End(xlDown).Offset(1)

Let me know if this helps.

 

[lancerj017]

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

 

[humboldtcountyguy]

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

 

[humboldtcountyguy]

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?

 

[lancerj017]

Try again:

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

 

[JMonty]

If that's the case then the following might work...

pst.PasteSpecial Paste:=xlPasteValues

Give it a go and let us know.

 

[humboldtcountyguy]

still not workin guys....i would be happy to upload the entire workbook...but im not sure how to on this site? is it available?