How to delete rows that had formulas before value

Question

I got an spread sheet that include formulas and I wrote a vb code to value paste.

Depending on the input file number of rows that filled is varied and I need to delete the rows those had formulas and now empty. (This is using as connector and otherwise it some how pick these extra rows which is unnecessary)

Sheet2.Range("G2:G298").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Above code not doing anything...

Answer 1

I'm not completely sure what you mean by "This is using as connector", but I believe it has to do with an export/import process to another application.

As mentioned, a zero length string is not the same as a truly blank cell. However, you can rid your worksheet of them easily. The fastest method I am aware of is a quick cyclic run through all of the columns, applying Text-to-Columns ► Fixed width ► Finish to each.

When that is done, the zero length strings will be reverted to truly blank cells but the worksheet's used range will still overlap those empty cells found at the bottom of the dataset. This means that any export to an external program will try to export those cells. Just run .UsedRange to get Excel to reevaluate the actual used range.

First, tap Ctrl+End to see what Excel thinks is the last used cell on the worksheet. Next, run the following macro.

Sub prep_for_export()
    Dim c As Long
    Debug.Print Sheets("Sheet1").UsedRange.Address(0, 0)
    With Sheets("Sheet1")
        For c = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
            .Columns(c).TextToColumns Destination:=.Cells(1, c), _
              DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        Next c
    End With
    Sheets("Sheet1").UsedRange
    Debug.Print Sheets("Sheet1").UsedRange.Address(0, 0)
End Sub

Edit Sheet1 in all four places if you have to before running it.

That is a little homogeneous but I think it should work for your purposes. After running the macro, tap Ctrl+End back at your worksheet again to see what Excel thinks is the last used cell on your worksheet. The before and after range addresses were recorded to the VBE's Immediate window as well.

Answer 2

If the blanks are results of a formula like:

=""

Entered into a cell and then copied and paste as values, those are not really blank cells.
Instead, those are cells that looks blank but contains zero length strings.
SpecialCells(xlCellTypeBlanks) and even Excel formula ISBLANK won't work on it.
One way is to loop through the range and check all that contains "" and delete it.

Dim c As Range, rngtodelete As Range
For Each c In Sheet2.Range("G2:G298")
    If Len(c.Value) = 0 Then
        If rngtodelete Is Nothing Then Set rngtodelete = c _
        Else Set rngtodelete = Union(rngtodelete, c)
    End If
Next
If Not rngtodelete Is Nothing Then rngtodelete.EntireRow.Delete xlUp

Another way is using AutoFilter like this:

Sheet2.Range("G2:G298").AutoFilter 1, "="
Sheet2.Range("G2:G298").SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp

I'm assuming that G2 does not contain your header but the start of your data.
If it happens to be your header, you'll need to use offset when deleting.

Sheet2.Range("G2:G298").Offset(1, 0) _
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp
Sheet2.AutoFilterMode = False