CODEWITHSUNDEEP
excelexcel-2007vba
user1624926
user1624926

Reputation: 451

Blanks rows after cutting from a filtered range

I'm using the below code to setup a filter and cut certain data from the filter and put it into another tab.

However I have two issues:

  1. When I Cut the data (I'm using .EntireRow.Cut) from the filtered sheet I'm left with blank rows in the filter. How can I cut the data without leaving blank rows?
  2. I want to Cut the filter data (Excluding the header in row 1). I can't use offset or .Resize as it takes me to a hidden row (not included in the filter range). How do I get around this?

Code is:

Lcol = FindLastCol(gcsCombinedKycExportsSheetName)
Lrow = FindLastRow(gcsCombinedKycExportsSheetName)
Set rngToCheck = Range(Sheets(gcsCombinedKycExportsSheetName).Cells(1, 1), _
    Sheets(gcsCombinedKycExportsSheetName).Cells(Lrow, Lcol)).Rows


FieldNum = Sheets(gcsCombinedKycExportsSheetName).Cells.Find(What:=gcsSearchFund, After:=[a1], LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column


Sheets(gcsCombinedKycExportsSheetName).Rows(1).AutoFilter


rngToCheck.AutoFilter Field:=FieldNum, Criteria1:= _
    "=*[2]*", Operator:=xlOr, Criteria2:="="


Lrow = FindLastRow(gcsCombinedKycExportsSheetName)
LrowRT = FindLastRow(gcsRemovedInvestors)


Sheets(gcsCombinedKycExportsSheetName).Range(Sheets(gcsCombinedKycExportsSheetName).Cells(1, 1), _
    Sheets(gcsCombinedKycExportsSheetName).Cells(Lrow, 1)).EntireRow.Cut Sheets(gcsRemovedInvestors).Cells(LrowRT, 1)

Upvotes: 1

Views: 533

Answers (1)

user1624926
user1624926

Reputation: 451

With the full support of Siddharth Rout I have managed to resolve this issue:

I have used the following:

.SpecialCells(xlCellTypeVisible)

This allowed me to select only the visable cells to cut.

I setup and dynamic range and used .Resize to unselect the header so I cut only the data that I had filtered.

Thanks again for support.

Ciaran.

Upvotes: 1

Related Questions