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)

 

[Keebler]

ok, so the "<>" means non blank - correct?

 

[shina67]

ok, so the "<>" means non blank - correct?

That's correct

 

[shina67]

is there a way to filter out the blank or empty rows?
is what you asked for in post #4.
that's what the additional code does.

 

[Keebler]

is there a way to filter out the blank or empty rows?
is what you asked for in post #4.
that's what the additional code does.

that is correct,
can you tell me why it minimized the "blank" rows?
perhaps I simply do not understand the vba

 

[shina67]

' Filter out blank or empty rows
Set rng = tws.Range("A3:A" & lrow)
rng.AutoFilter Field:=1, Criteria1:="<>"

The filter is applied to Column A and filters out and blanks

 

[Keebler]

so that is the desired effect?
to minimize the empty rows?

i really would have preferred it to delete the empty rows over minimizing them... (sorry, i did not know that would have been an issue)

 

[shina67]

.  
Sub copyto_test()
  
    Dim lrow As Long, srow As Long, crow As Long
    Dim slist As String, srng As String, trng As String
    Dim aws As Worksheet, tws As Worksheet
    Dim rng As Range
    
    Set aws = ActiveSheet
    Set tws = Sheets("INDEX")

    lrow = tws.Range("E" & tws.Rows.Count).End(xlUp).Row
    
    If lrow <= 3 Then
        trng = tws.Range("A3").Address
    Else
        trng = tws.Cells(lrow + 1, 1).Address
    End If

    crow = aws.Range("E" & aws.Rows.Count).End(xlUp).Row
    srow = aws.Range("H" & aws.Rows.Count).End(xlUp).Row
    
    srng = "K" & srow & ":AQ" & crow
    slist = "K" & srow & ":AQ" & crow

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

    ' Filter out blank or empty rows in the target worksheet
    Set rng = tws.Range("A3:A" & lrow)
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    tws.AutoFilterMode = False
  
    
    Application.CutCopyMode = False

End Sub

The code has been amended to the following:- ' Filter out blank or empty rows in the target worksheet
Set rng = tws.Range("A3:A" & lrow)
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

This should now delete the rows instead of filtering them as per your original request.

 

[Keebler]

so, i got the row delete to work with this

Sub delblnkrows_test4() 'worked
Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("INDEX")
Set rng = ws.Range("a3:a20")

rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

 

[Keebler]

this still does not work when the sheet is populated with even the simplest of data (involving 3 columns "a" "q" and "aa")
I keep getting an error that no cells where found when I can clearly see at least 12 (just on the viewable sheet)

Sub delblnkrows_test4() 'worked
Dim tws As Worksheet
Dim lrow As Long, i As Long
Dim rng As Range

Set tws = Sheets("INDEX")
'lrow = tws.Range("e1") 'gets the last row of destination ws
Set rng = tws.Range("a3:a91")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

so what am i missing?

 

[shina67]

this still does not work when the sheet is populated with even the simplest of data (involving 3 columns "a" "q" and "aa")
I keep getting an error that no cells where found when I can clearly see at least 12 (just on the viewable sheet)

Did you try the new code I put on post #14. You had said it worked before but needed the filter removing and delete empty rows instead. The above code in post #14 does that b