James Snyder
Well-known Member
- Joined
- Jan 11, 2013
- Messages
- 618
I am losing the last row of data when using '.SpecialCells(xlCellTypeVisible).Copy'. Since this is not a range I set, I am wondering if others have found this and have a workaround. Below are links to screenshots showing the problem:
Losing NULLs 1 - Bermex's library
Losing NULLs 2 - Bermex's library
Losing NULLs 3 - Bermex's library
Since the .AutoFilter catches everything as it should, it seems to be a failure of the .SpecialCells function or the labeling of the last row as 'visible'. Any help?
Losing NULLs 1 - Bermex's library
Losing NULLs 2 - Bermex's library
Losing NULLs 3 - Bermex's library
Code:
With ftpWkSht
' If we have a valid workbook and a valid worksheet, find the bottom row
ftpMax = .UsedRange.Rows.Count
Set ftpRange = .Range(.Cells(1, 1), .Cells(ftpMax, ftpMaxCol).End(xlUp))
With ftpRange
.AutoFilter
.AutoFilter field:=meterRdCol, Criteria1:=vbNullString ' or "="
' Make a new sheet and copy filtered data to it to be able to address each row
.SpecialCells(xlCellTypeVisible).Copy
Set filtSheet = Sheets.Add(After:=Sheets(1))
filtSheet.name = fltShtName
filtSheet.Paste
Application.CutCopyMode = False ' Clear clipboard of copied data