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:
.AutoFilter Field:=4, Criteria1:="MyCriteria"
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:
Call DelFilterParam(Sheet1, "MyTable", 4, "MyCriteria")
Sub DelFilterParam( _
ByVal wsSheet As Worksheet, _
ByVal stTable As String, _
ByVal iField As Integer, _
ByVal vCriteria As Variant)
.AutoFilter Field:=iField, Criteria1:=vCriteria
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.
Here’s a “Yahtzee” style dice game in Excel; quickly developed, just for a bit of fun. Usual scoring rules apply – roll 5 dice up to 3 times, and score in each of the categories.
Click on dice number to “hold” that die, click on the blank score space to save roll score, then roll again. The high score table will show the 6 highest overall scores.
YTZ Game for Excel – click to download
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:
Dim rng As Range
Dim lRow As Long
Dim strOutput As String
Dim strNextLine As String
Dim strFilename As String
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
strOutput = strOutput & vbCrLf & strNextLine
Set adStream = New ADODB.Stream
.Type = adTypeText ' or use adTypeBinary
.Charset = "us-ascii" 'See table below
.SaveToFile strFilename, adSaveCreateOverWrite ' 2; adSaveCreateNotExist = 1
'The contents of Charset are case-insensitive. The default value is "us-ascii".
Code posted with his permission. Thanks, Bernie!
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.
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
stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
If stOutput = "" Then
stOutput = stNextLine
stOutput = stOutput & vbCrLf & stNextLine
Set fso = CreateObject("ADODB.Stream")
.Type = 2
.Charset = stEncoding
.SaveToFile stFilename, 2
Set fso = Nothing
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.
If you’re anything like me, you often have several workbook projects open at one time, a mix from barely developed to pre-deployment beta.
And again, if you’re as forgetful as I am, you have run into a situation where you’ve entered debug part way through some faulty code, been distracted by something else and forgotten that the code which was running had disabled screenupdating, or events, or similar. Then switched blithely to another workbook, and been puzzled as to why your events aren’t firing, or why nothing is calculating as it should… Then spent ages pondering over perfectly good code, only to eventually remember that your last routine aborted and left events disabled. Or a cryptic progress message in your status bar. Or no error messages displaying ever again…
Well, maybe it’s only me! But, in case you recognise this, here’s a tip:
If Application.Workbooks.Count = 1 Then
MsgBox "There are no open workbooks." & vbCr & vbCr & "Application cannot be reset.", vbExclamation + vbOKOnly, "XLSM | Invalid Procedure / Call"
On Error GoTo ErrCatch
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
.DisplayFullScreen = False
MsgBox "An unknown arror occured while running code in Personal.xlsb." & vbCr & vbCr & _
"If this problem persists, throw the PC out of the window.", vbCritical + vbOKOnly, "XLSM | Error"
A bit of code like this saved to your Personal Workbook, with a nice little icon in the QAT – quick access toolbar – means that when light begins to dawn that you carelessly left the application somewhat undone, you can restore more normal settings with a simple click.