2

I'm stuck with this part of my code and just can't get passed it:

'Sort Active Sheet By Property Case Number
Sheet2.Range("A1:AQ" & Sheet2.UsedRange.Rows.Count).Sort Key1:=Sheet2.Range("Z1"), Order1:=xlAscending, Header:=xlYes

'Remove Duplicate Property Case Number
Sheet2.Range("A1:AQ" & Sheet2.UsedRange.Rows.Count).RemoveDuplicates Columns:=26, Header:=xlYes

'Add Formula To Detect blank rows. This is a fix for the mistery blank rows that appear at this point
If Sheet2.UsedRange.Rows.Count > 1 Then
Sheet2.Range("AR1").Value = "Temp"
Sheet2.Range("AR2").Formula = "=IF(COUNTA(A2:AQ2)<>0,""Not Empty"","""")"       '"=IF(COUNTA(A2:AQ2)=0,""Not Empty"","""")"
Sheet2.Range("AR2").AutoFill Destination:=Sheet2.Range("AR2:AR" & Sheet2.UsedRange.Rows.Count), Type:=xlFillSeries
Sheet2.Columns("AR").Copy
Sheet2.Columns("AR").PasteSpecial Paste:=xlPasteValues
Sheet2.Range("A1:AR" & Sheet2.UsedRange.Rows.Count).Sort Key1:=Sheet2.Range("AR1"), Order1:=xlAscending, Header:=xlYes

Sheet2.Columns("AR").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Sheet2.Columns("AR:AR").Delete
End If

The problem here is when the code executes ".RemoveDuplicates Columns:=26, Header:=xlYes" it removes the duplicate rows but those rows are still included in the sheet1.usedrange.rows.count as if they have invisible contents or something.

So now I had to add a code to remove those invisible rows and exclude them from usedrange count. I tried doing a ".SpecialCells(xlCellTypeBlanks).EntireRow.Delete" but I keep getting a ERROR 400 and I can't see any problems with my code for me to be getting that error. By the way, I am the kind of programmer who prefers not to use looping codes that checks rows one by one, I want to make a program that runs as fast as possible.

Community
  • 1
  • 1
Jay
  • 590
  • 6
  • 13
  • 29
  • 1
    Don't use `.UsedRange.Rows.Count` Find the last row using [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) and then construct your range to sort/delete. – Siddharth Rout Mar 28 '14 at 03:25
  • Are you sure the cells are actually blank? After removing the duplicates, have you tried to manually find the blank cells by pressing F5? – Excel Developers Mar 28 '14 at 09:32
  • Thank's for the suggestions guys, I found another way around it, I just used a filter to take out the blank cells. Yes those were blank cells but usedrange seem to still include them – Jay Mar 30 '14 at 02:30

0 Answers0