Search TeachExcel.com

Video Tutorials

Tutorials: Macros

Featured Tutorial

Dynamically Update Drop-Dowm Menus in Excel

Featured Macro

Delete Empty Rows in Excel

Excel Macro VBA Tip 3 - Using The Range Property in Excel Macros to Select Columns and Rows

Video | Similar Helpful Excel Resources

This Excel VBA Macro tutorial includes a description of how to selection entire rows and columns in Microsoft Excel using the Range property of Visual Basic. This Excel tip video shows you how to select individual rows and columns as well as multiple variations of rows, columns, and individual cells all at once. This is a great tutorial for those just starting out with Excel Macros.

This is a great Excel vba tutorial for beginners learning how to use the Range property in Excel macros.

Topics Covered

Excel VBA - Excel Macros

Range Property in Excel Macros

Selecting Rows, Columns, and Cells with an Excel Macro.

Difficulty: Intermediate
Video: Yes

Got a Question? Ask it Here in the Forum.

Similar Helpful Excel Resources

I Use Excel 2003. There Are Six Columns (2000 Rows Deep) And Each Cell Contains A Number From 1 To 50. I Wish To Know How Many Times A Combination Of Two Numbers Appears In All The Rows. E.g If I Select The Two Numbers 14 And 47 I Want The Formula T - Excel

View Content

I use Excel 2003.

There are six columns (2000 rows deep) and each cell contains a number from 1 to 50.
I wish to know how many times a combination of two numbers appears in all the rows. e.g if I select the two numbers 14 and 47 I want the formula to tell me how many times those two numbers appear in all of the rows.
Thank you.
Colwyn.

Extended Range (columns & Rows) In Macros - Excel

View Content

Hi Everybody

I Record this Macro. To Make Sheets from Master Sheet. ( Data in column A)

Now i want to Modifiy this code ,if the Rows and Column will be Extend in Master Sheets.

Means Right now i have only 50 rows and 14 Columns. And in future if i Add 100 rows and 3 columns will it work for me or not.

If Not how can i modifiy this Code.

Basically, i want to make Sheets from data from (A:A) from Master Sheets in the Same workbook.

Actually, i am Learning how to record or Modifiy the Macros.

HTML Code:

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 7/16/2009 by AAditya
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveWindow.SmallScroll Down:=-105
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="CZ001"
    Cells.Select
    ActiveWindow.SmallScroll Down:=-15
    Range("A1:L84").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    ActiveWindow.SmallScroll Down:=9
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=-27
    Selection.AutoFilter Field:=1, Criteria1:="CZ002"
    Range("A1:L84").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=9
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet3").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
End Sub

Thanks in Advance

Hardeep Kanwar

Excel Macro To Select Cell Range - Excel

View Content

I have a variable data in Range D1 to D10. I need a macro that selects only the cells that has the data. (Since the range is variable)

Eg1: if i have the data from D1 to D3, then the macro should select only from D1 to D3.

Eg2: If i have the data only in D1, then the macro should select only D1.

Your help is very much appreciated..

Excel Macro To Select And Cut Multiple Rows To A New Worksheet - Excel

View Content

I run a report every week and would like to cut the rows for a particular date range into a new worksheet (i.e. all rows with transactions between the 16th of one month to the 15th of the following month).

I'd like to cut all rows between 07/16 and 08/15 and past them in a new worksheet called "Aug" And cut all rows between 08/16 and 09/15 and past them in a new workseet called "Sept"

Example data
Column A - Column B - Column C
Post Date - Name - Vendor
07/25/10 - Wilson - Starbucks
08/05/10 - Wilson - American Airlines
08/14/10 - Guang - Apple Store
08/15/10 - McGee - Staples
08/17/10 - Curry - AT&T
09/02/10 - Johnson - Exxon
09/11/10 - Simms - Old Navy

Below is the VBA code I'm using but i'm getting an error on the red line......ANY SUGGESTIONS??????

Dim workingrow As Range

For Each workingrow In Sheets ( "Main" ). Rows

If (workingrow. Cells ( 1 , 1 ). Value >= DateValue ( "7/16/2010" )) And _
(workingrow. Cells ( 1 , 1 ). Value

Excel Macro To Select And Pasting Similar Rows - Excel

View Content

Here is the spreadsheet:

AHD01 1341
AHD01 1345
AHD01 1346.75
NID01 7850
NID01 7875
NID01 7881
AOA01 119.72
AOA01 119.74
AOA01 119.75
AOA01 119.77
AOA01 119.78
AOA01 119.79
GYF01 97.475
GYF01 97.52
GYF01 0
GYF01 0
GYF01 0
KQR01 97.61
KQR01 97.625
KQR01 97.64
KQR01 97.645
KQR01 97.61

How do i create a macro so that it copies all the rows with eg. AHD01 to a new spreadsheet, and NID01 to another new spreadsheet; and AOA01 to another new spreadsheet, etc, etc ?

Using Select With Range.cells Property - Excel

View Content

Can anyone tell me why:

Range(Cells(iNameR, iNameC + 3), Cells(iNameR, iNameC + iCount + 2)).Select

works while:

Range(Cells(iTargetR, iTarDateC)).Select

returns the runtime error '1004' method 'Range' of object '_Global'

TIA

--
remove 901 from reply email for valid address.

heidecke@901shaw.ca
-----
remove

Range Select With Activecell And Offset Property - Excel

View Content

I need to clear the contents of a certain range and yes I could just write the code as follows: Range("a1:b240").clearcontents

however, the range selection depends on the active cell. I have tried entering the following: Range(activecell, activecell.offset(1,240)).clearcontents
But I keep receiving a runtime error '1004'. Any ideas on how to fix this? Thanks....AJ

Select Range Using Offset Or Resize Property - Excel

View Content

Hi!!

So in my sheet, I have selected column D. And I want to select the next 4 columns (including column D), so that my selection or active cells at that point would be Columns D, E, F, G and H.

All help is very appreciated! THanks!

Excel 2003- Macro To Sum Rows & Columns - Excel

View Content

Hi,

I've searched through the various help posts but couldn't find a macro I need & thus ask for help. I have an excel sheet and Column 'A' & Row 'A' are populated with titles (headers). Data in the sheet is populated every day so rows and columns keep changing. I need an excel VBA macro to automatically calculate the sum of each column vertically (ie starting from column 'B' till last row with data) and also to calculate the sum of each row horizontally (ie starting from column 'B' till last column with data). Totals are to be included in the next empty column and row. Many thanks in advance for your help & support.

Macro To Select Only Rows With A Value In A Range - Excel

View Content

I'm in charge of taking a report; streamlining its functions; and automating future reporting.

I'm using a database and some of the reporting function Access has. For another variation I'm forced to export the query to excel and transpose the vertical layout horizontally.

I'm making the entire process automated via a macro. The code is too long to post in its entirety but here is an example of what I'm trying to accomplish:

Code:

Sub dataCopy()
Range("H2:T500").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
End Sub

All the cells in H2:T500 can potentially contain data; what I need to do is change this so that only the rows that contain a value be selected for copying.

I'm sure it's possible but not sure about the code that would accomplish this. Any takers?

~Len