Posted on

Filter table, then delete filtered rows

It’s a pretty common requirement to filter out some values in an Excel table, then delete those rows from the table. It should be straightforward to do this with a little VBA, but it seems to catch lots of people out! Here’s one simple method:

Sub DelFilter()
    With MySheet
        With .ListObjects("MyTable").DataBodyRange
            .AutoFilter
            .AutoFilter Field:=4, Criteria1:="MyCriteria"
            .EntireRow.Delete
            .AutoFilter
        End With
    End With
End Sub

You just need to change the sheet codename, the table name, the field index number, and the criteria value.

It would be simple to replace the user selected values with variables or parameters, so the code becomes more reusable:

Sub TestDelFilter()
    Call DelFilterParam(Sheet1, "MyTable", 4, "MyCriteria")
End Sub


Sub DelFilterParam( _
    ByVal wsSheet As Worksheet, _
    ByVal stTable As String, _
    ByVal iField As Integer, _
    ByVal vCriteria As Variant)
    
    With wsSheet
        With .ListObjects(stTable).DataBodyRange
            .AutoFilter
            .AutoFilter Field:=iField, Criteria1:=vCriteria
            .EntireRow.Delete
            .AutoFilter
        End With
    End With
End Sub

This is a simple example, applying a very simple filter. But it’s usually the method for deleting the filtered rows which catches people out, and this approach makes it easy.

Posted on

Write Excel Data to Text File – Early Binding

Following on from my last post Using VBA to Write Excel Data to Text File – I’ve had this reply from Bernie Deitrick, Excel MVP, who has written a modified version of the code for when early binding is preferred:

Sub WriteTextFileEarlyBinding()
    Dim rng As Range
    Dim lRow As Long
    Dim strOutput As String
    Dim strNextLine As String
    Dim strFilename As String
    Dim strSeparator
    Dim adStream As ADODB.Stream

    'CHANGE THESE PARAMETERS
    strFilename = ThisWorkbook.Path & "\Output.txt"
    Set rng = ActiveSheet.UsedRange    'Selection
    strSeparator = vbTab   ' e.g. for comma seperated value, change this to strSeparator = ","

    For lRow = 1 To rng.Rows.Count
        strNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), strSeparator)
        If strOutput = "" Then
            strOutput = strNextLine
        Else
            strOutput = strOutput & vbCrLf & strNextLine
        End If
    Next lRow

    Set adStream = New ADODB.Stream

    With adStream
        .Type = adTypeText  ' or use adTypeBinary
        .Charset = "us-ascii"    'See table below
        .Open
        .WriteText strOutput
        .SaveToFile strFilename, adSaveCreateOverWrite    ' 2; adSaveCreateNotExist = 1
    End With

End Sub

'Constant            Value
'CdoBIG5            "big5"
'CdoEUC_JP          "euc-jp"
'CdoEUC_KR          "euc-kr"
'CdoGB2312          "gb2312"
'CdoISO_2022_JP     "iso-2022-jp"
'CdoISO_2022_KR     "iso-2022-kr"
'CdoISO_8859_1      "iso-8859-1"
'CdoISO_8859_2      "iso-8859-2"
'CdoISO_8859_3      "iso-8859-3"
'CdoISO_8859_4      "iso-8859-4"
'CdoISO_8859_5      "iso-8859-5"
'CdoISO_8859_6      "iso-8859-6"
'CdoISO_8859_7      "iso-8859-7"
'CdoISO_8859_8      "iso-8859-8"
'CdoISO_8859_9      "iso-8859-9"
'cdoKOI8_R          "koi8-r"
'cdoShift_JIS       "shift-jis"
'CdoUS_ASCII        "us-ascii"
'CdoUTF_7           "utf-7"
'CdoUTF_8           "utf-8"
'The contents of Charset are case-insensitive. The default value is "us-ascii".

Code posted with his permission. Thanks, Bernie!

Posted on

Using VBA to Write Excel Data to Text File

It’s a fairly common requirement to need a bit of VBA code to write Excel data to a text file.  There are several methods you can use to do this, but here’s an example which I like to use, as it offers good control over the range of data, the structure and format of the text output and – unusually – control over the text encoding / character set. It is really simple to specify UTF-8, UTF-16, ASCII, ISO8859, etc.

Sub WriteTextFile()

Dim rng As Range, lRow As Long
Dim stOutput As String, stNextLine As String, stSeparator As String
Dim stFilename As String, stEncoding As String
Dim fso As Object

'-------------------------------------------------------------------------------------
'CHANGE THESE PARAMETERS TO SUIT
Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
stFilename = "C:\Temp\TextOutput.txt" 'this is the text file path / name
stSeparator = vbTab 'e.g. for comma seperated value, change this to ","
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
'-------------------------------------------------------------------------------------

For lRow = 1 To rng.Rows.Count
    If rng.Columns.Count = 1 Then
        stNextLine = rng.Rows(lRow).Value
    Else
        stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
    End If
    If stOutput = "" Then
        stOutput = stNextLine
    Else
        stOutput = stOutput & vbCrLf & stNextLine
    End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
    .Type = 2
    .Charset = stEncoding
    .Open
    .WriteText stOutput
    .SaveToFile stFilename, 2
End With
Set fso = Nothing

End Sub

Change the parameters to suit your needs – you can use any code you like in this section, as long as the four variables rng, stFilename, stSeparator and stEncoding are assigned.