Forum Discussion
VBA - Need Help Please - not all rows are copied
Hi Guys, Need your help, please. I have this VBA code below and it's basically a dynamic filter that copies the filtered data into a new sheet. However, the last 3 rows from my data are not copie...
Is your data contiguous? Where does a control+arrow-down take you, starting from cell A1?
my Cell A1 to A4 is blank data since I'm having my data starts at A5
- BTW, it appears that everything before:
ThisWorkbook.Sheets("CustomerAmt").Cells.clearcontents
is acting on "Sheet4" and has no affect on the copy/paste
and then after that you have:
ActiveSheet.Range("A5").AutoFilter field:=7, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic
orig.Range(Cells(5, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
which will filter for LAST month and then only copy visible so is it that the last 3 rows are THIS month? - Suppose you have 95 rows worth of data (this is cells A5:A99).
the counta function of your range correctly returns the number 95.
But further down, you try to access the range like so:
Range(Cells(5, 1), Cells(count_row, count_col))
This gives you the range starting from row 5, up to the cell of row 95 (!), thus omitting the rows 96-99
The syntax you are looking for is:
Cells(5, 1).resize(count_row, count_col))
