Excel Howto?
Excel VBA how to?

-Aviral Mittal




1. How to know what 'Names' are present in Excel Sheet.
Excel Green Ribbon -> Formulas -> Name Manager
You will see all the Defined Names.
These names are primarily used for Data Validation Lists.

2. How to use 'quotes' with in a string that needs to be used/printed.
MsgBox "Just use double quotes for example ""this will be printed with quotes"" but this is w/o quotes"

3. How to write a value to a cell in a named sheet?
Dim mRow as long
dim mCol as long
mRow = 2
mCol = 5
ThisWorkbook.Sheets("SheetName").Cells(mRow,mCol).Value = "Desired Value"
OR with Sheet Index
ThisWorkbook.Sheets(1).Cells(20, 20).Value = "abcd"
instead of Value,


4. How to clear en entire Column?
ThisWorkbook.Sheets("SheetName").Range("A:A").ClearContents
Note: In the above example, instead of using ClearContents, only 'Clear' can also be used, this will also clear any existing format of this range.

5. How to clear/delete values from entire Row?
ThisWorkbook.Sheets("Lists").Rows(20).ClearContents

6. How to Delete the row itself?
ThisWorkbook.Sheets("Lists").Rows(20).Delete

7. How to delete the entire column:
ThisWorkbook.Sheets("Lists").Columns(20).Delete

8. How to find last used Column

dim rowNo as long
rowNo = 1
MsgBox ThisWorkbook.Sheets(1).Cells(rowNo,ThisWorkbook.Sheets(1).Columns.Count).End(xlToLeft).Column
The above will find the last used column in first row. Change rowNo, to find last used column in that row.

9. How to find last used Row
dim colNo as long
colNo = 1
MsgBox ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, colNo).End(xlUp).Row
The above will find the last used row in first Column. Change the colNo to find last used row in that column.

10.  How to get the 'grey' default borders back in Excel sheet using VBA.
At times while copying, or updating cell values, its possible that the grey default boders of cell or a range are lost
And it just annoys you.
To get it back, here is the VBA code

      Range("D:D").Borders.LineStyle = xlNone
      Range("D:D").Interior.ColorIndex = xlNone

This will set the entire Column of D to its original default border style.


11. Excel VBA: How to dump an entire column into a text file:

    Dim oFile As String
    Dim lastRow As Long
    Dim cellVal As String
    oFile = "C:\Users\avis\Desktop\output.txt"
    Open oFile For Output As #1
    lastRow = ThisWorkbook.Sheets("output").Cells(sht.Rows.Count, 1).End(xlUp).Row
    'MsgBox lastRow
   
   
    For ii = 1 To lastRow
      cellVal = ThisWorkbook.Sheets("Output").Range("A" & ii).Value
      Print #1, cellVal
    Next ii
    Close #1

The above code dumps entire column A from a sheet named "Output" to O/P file "C:\Users\avis\Desktop\output.txt"
Note the hard codings. The column number '1' is hard coded in the line:
lastRow = ThisWorkbook.Sheets("output").Cells(sht.Rows.Count, 1).End(xlUp).Row
The column character 'A' is hardcoded in the line:
cellVal = ThisWorkbook.Sheets("Output").Range("A" & ii).Value
Both of these must be changed to change the column that needs to be dumped

12. How to avoid quotes while dumping values into output file
Use Print, instead of Write
Print #1, cellVal
Instead of
Write #1, cellVal

13. How to get first character form a string:
dim myStr as String
myStr = "Ganystring"
MsgBox Left(myStr,1)

14. How to remove all spaces from a string
dim tryStr as String
tryStr = "abcD efgH ijkL"
MsgBox Replace(tryStr, " ", "")

15. How do I switch sheets? How to I work with a sheet without activating it?
How do I refer to a sheet without naming it again and again?

This is simple use the following

  Dim shtName as String
  Dim ColumnHeading as string
  Dim mCell As Range
  shtName = "Sheet1"
  CoulmnHeading = "Country"
  With Sheets(shtName)
    Set mCell = .Rows(RowNum).Find(What:=ColumnHeading, lookat:=xlWhole)
    MsgBox mCell.Address
  End With

Notice the .Rows(dot rows) which will now refer to the Rows in the above sheet "Sheet1"

16. How do I use Excel Built in Functions/Formulas in VBA?

Just use 'WorksheetFunction.' as prefix. for example to use the function/formula Dec2Hex, do the following:

MsgBox WorksheetFunction.Dec2Hex(12345)


<- Previous                                                                                                     Next ->

Key Words: Excel Names, Excel Lists, Delete Excel Names, Delete Excel Named Lists, Delete Named Data Validation Lists. Excel File I/O, dump into txt file, write vs print
Excel vba last used row, Excel vba last used column, Excel VBA delete row, Excel VBA delete column