AZ

recently updated or created

June 28, 2004
Excel forum
' our developpers or any visitor can help you with some ideas to get your code work...

Jan 11, 2004
VBA functions
' with descripion, syntax, and working examples

functions : Abs, Left, Right, Mid, CDate, CDbl, CInt, Day, FormatNumber, Month, Trim, UCase, Weekday, Year, ...

Apr 29, 2002
delete cell, row, column

' basic cell deleting

ActiveCell.Delete Shift:=xlUp

Selection.Delete Shift:=xlToLeft

' delete entire row or col

Range("E10").Select ' or any other range
Selection.EntireRow.Delete

Range("F10").Select ' or any other range
Selection.EntireColumn.Delete

clear contents of cell without shifting

Selection.ClearContents

sponsored links

Dec 3, 2001
read from external formatted text file 

' Read a text file with several rows and differing number of columns into the active worksheet with a pre-defined cell format. 
Source code ReadTextFile.bas

Dec 3, 2001
looping 

' basic sheet hopping

' tells the name of each sheet in the current workbook
For Each mySheet In Sheets
  ' have your real code here
  MsgBox mySheet.Name 
  If mySheet.Name = "Sheet4" Then
    Exit For
  End If
Next mySheet

Dec 1, 2001
some hints

' use the system dialogbox to select a text or other file
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

' are you using SendKeys ... and need your application wait for proper issuing...
DoEvents passes control to the operating system. Control is returned after
the operating system has finished processing the events in its queue and all
keys in the SendKeys queue have been sent.

' use worksheet functions if VBA has no direct function of its own
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

' knowing the column number from its name. e.g.  CU=99
MsgBox Range("CU:CU").Column

Nov 30, 2001
Copying around >> more

' from sheet to sheet 
Worksheets("myOriginSheetName").Range("A1:B7").Copy _
    destination:=Worksheets("myDestinationSheetName").Range("C1")

Moving around >> more

' move number of rows down 
ActiveCell.Offset(7).Activate ' use either the .Activate or the .Select method

' move number of rows up 
ActiveCell.Offset(-2).Select ' you get an error if in the first row

Nov 29, 2001
More about Selecting Cells in the current sheet
'the last (most right, most down) cell
ActiveCell.SpecialCells(xlLastCell).Select

Nov 28, 2001
Selecting Cells in the current sheet
'a single cell
Range("A1").Select

'a contiguous range
Range("B6:D15").Select

'a non-contiguous range
Range("A1,B6:D15,F2").Select

'an entire row
Rows("1").Select ' the name of the row
Rows(5).Select ' the 5th

'an entire column
Columns("1").Select ' the name of the column : ERROR
Columns("B").Select ' the name of the column
Columns(3).Select ' the 3rd column

Nov 27, 2001
Moving to the next free cell
Range("A1").Select ' assume A1 is active cell
Selection.End(xlToRight).Select ' use Excel function to find last in a row
ActiveCell.Offset(0, 1).Activate ' move one cell to the right

'or the short version
Range("A1").End(xlToRight).Offset(0, 1).Select
' be sure some data is in the first row or you get an error

Microsoft Excel 2002 Power Programming 
with VBA
984 pages + CD-ROM

[buy it now at Amazon]

Microsoft Excel 2000 Power Programming 
with VBA
869 pages + CD-ROM

[buy it now at Amazon]