I am attempting to filter out blank cells of an active column which has 172 rows.
The code need to work across all columns as it transferring the info to another sheet as part of a backup,
please see code below.
Dim col as String
Dim originalsheet as String
Dim lastrow as String
Dim erow as long
originalsheet = " Master Role Profile"
col = split(ActiveCell.Address(1,0), "$")(0)
lastrow = sheets(originalsheet).Cells.SpecialCells(xlCellTypeLastCell).row
sheets(originalsheet).Range(col).AutoFilter field:= col , Criterial1:="<>" I'm receiving error Run-time error 1004 application defined or object defined error
This part works for the copy and past for back up, but takes a long time to go through all 164 cells, this why I would like to filter out the Blanks prior to the to the copy and paste.
For i = 8 to lastrow
erow= Sheets(originalsheet)Cells(1,col).row
sheets(originalsheet).past destination:=Sheets(backup).Cells(erow,col)
next i
Application.CutCopyMode = False
Sheets(originalsheet).ShowAllData
Sheets(originalsheet).autofiltermode = False
looking for some assistance.
The code need to work across all columns as it transferring the info to another sheet as part of a backup,
please see code below.
Dim col as String
Dim originalsheet as String
Dim lastrow as String
Dim erow as long
originalsheet = " Master Role Profile"
col = split(ActiveCell.Address(1,0), "$")(0)
lastrow = sheets(originalsheet).Cells.SpecialCells(xlCellTypeLastCell).row
sheets(originalsheet).Range(col).AutoFilter field:= col , Criterial1:="<>" I'm receiving error Run-time error 1004 application defined or object defined error
This part works for the copy and past for back up, but takes a long time to go through all 164 cells, this why I would like to filter out the Blanks prior to the to the copy and paste.
For i = 8 to lastrow
erow= Sheets(originalsheet)Cells(1,col).row
sheets(originalsheet).past destination:=Sheets(backup).Cells(erow,col)
next i
Application.CutCopyMode = False
Sheets(originalsheet).ShowAllData
Sheets(originalsheet).autofiltermode = False
looking for some assistance.