Home

Move down one row with hidden or filtered rows with Excel VBA

Here's some Excel VBA code that allows you to 'move' down (i.e. select/activate) a single visible row (or multiple if you adjust to code) that takes into account hidden or filtered rows.

Sub sMoveDown1Row()
    
    'Select row to start on
    .Range("A1").Select
    'Go down 1 row
    ActiveCell.Offset(1, 0).Select
    'Keep going until on a row that isn't hidden
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

VBA Functions using DAO to determine if a Table, Query, Field (and its properties) exist

VBA Functions that use DAO to do the following:

IsTable(): Determine if table exists in an Access database
IsQuery(): Determine if query exists in an Access database
IsField(): Determine if field exists in an Access database table/query
IsFieldProperty(): Determine if property for a table/query field exists

Example Usage

See ExampleUsage() in 'modExample' in the sample file for complete examples of how to use the functions.

Debug.Print "IsTable('" & strName & "') = " & IsTable(strName)

'or 

Dim db as DAO.Database, strName as string
Set db = CurrentDb
strName = "tblData"
Debug.Print "IsTable('" & strName & "', db) = " & IsTable(strName, db)

VBA DAO Fields Tables Queries Properties

Using LaTex in Microsoft Word

I just found out that Microsoft Word supports using LaTex to enter formulas, as well as UnicodeMath through options in the Math Ribbon.

The last time I used LaTex was when I was a graduate student (which was some time ago), so I spent a day re-familiarizing myself with LaTex syntax by creating my own reference guide. I found a LaTex package, Atom-LaTex, that works with code editor of choice, i.e. Atom. Then after much Googling and time figuring stuff out I managed to churn out a reference document for myself.

Microsoft Word LaTex UnicodeMath Atom MathJax

Unhide / Delete all defined named ranges

Looking for external links but can't find any in formulas in your workbooks? Defined named ranges can also contain external links, and you've deleted what you can from the name manager but you're still getting messages about external links in your workbook. Sometimes there are hidden defined named ranges. Here are two VBA subroutines; one to unhide ALL defined named ranges and another to delete all of them.

Also, check out this awesome 'FindLink' add-in which is great at find external links in your workbook in objects, defined name ranges, and more!!! I had started to build a spreadsheet that did what this tool does, but gave up after finding it since it does what I had managed to do and then some.

Sub UnhideAllDF()

    'Unhide all defined names in the active workbook
    For Each df In ActiveWorkbook.Names
        df.Visible = True
    Next

    MsgBox "Unhid all defined names", vbInformation
	
End Sub

Microsoft Excel VBA Named Ranges

Sidebar

Welcome!

Latest Articles

Most Read

Top Ten Tags

Who's Online