To young people in the workplace: Your duty is to level the land, not to worry about time. You do the March and April things, and in August and September you have your own answers. Try to improve yourself!.
The VBA Code Solution is my first tutorial and is currently in its second revision. This set of tutorials is aimed at improving after the introduction, and in the process of learning this set of tutorials, the focus is on understanding and mastering my "building block programming" thinking. Use the examples in the tutorial flexibly like building blocks to lay out your favorite code.
This set of tutorials consists of three volumes, one hundred and forty-seven lectures, covering a wide range of content, but also an excessive tutorial between beginner and intermediate, the revised content is mainly to provide provider source code files and code corrections to 32-bit and 64-bit dual-use code. In the future, it will be launched for everyone one after another. Today's content is Lecture 5: The Use of offset functions
<h1 class="pgc-h-arrow-right" > the use of the OFFSET function in VBA in Lecture 5</h1>
Today continue to talk about the fifth lecture of VBA practical code, I believe you have learned a lot through this series of articles, today focus on the use of OFFSET functions in VBA.
<h1 class="pgc-h-arrow-right" >1 Move the active cell up and down</h1>
1） Range("A32", Range("A32"). End(xlUp)). Select
Range(ActiveCell, ActiveCell.End(xlUp)). Select
Statement description: After executing the above code, select the currently active cell up to the first non-empty cell.
Note: ActiveCell refers to the currently active cell.
2） Range("A1", Range("A1"). End(xltoRight)). Select
Range(ActiveCell, ActiveCell.End(xltoLeft)). Select
Statement description: After executing the above code, select the current active cell to the right to the first non-empty cell.
Note: "xlTORight" instead of "xlRight"
<h1 class="pgc-h-arrow-right" >2 Use the OFFSET function to move cells</h1>
3） Range("A2", Range("A2"). Offset(0, 10)). Select
Range(ActiveCell, ActiveCell.Offset(0, 10)). Select
Statement description: After executing the above code, select the currently active cell to the right to cell 10.
Note: The OFFSET function can think of him as a cell property, the Range.Offset property. Returns a Range object that represents a range at a certain offset position in the specified range of cells.
Expression: Offset(RowOffset, ColumnOffset)
where the parameters: RowOffset, ColumnOffset represent offset rows, columns. Numbers can be positive, negative, zero
RowOffset Optional The number of rows in the area offset (positive, negative, or 0 (zero)). Positive numbers indicate downward offsets, and negative numbers indicate upward shifts. The default value is 0.
ColumnOffset The number of columns for the optional area offset (positive, negative, or 0 (zero)). Positive numbers indicate offset to the right, and negative numbers indicate offset to the left. The default value is 0.
4） Range("A20", Range("A20"). Offset(0, -10)). Select
Range(ActiveCell, ActiveCell.Offset(0, -10)). Select
Statement description: After executing the above code, select the currently active cell to the left to cell 10.
Note: ColumnOffset represents the number of columns for the region offset, and negative numbers represent the left offset.
5） Range("a2", Range("a2"). Offset(10, 0)). Select
Range(ActiveCell, ActiveCell.Offset(10, 0)). Select
Statement description: After executing the above code, select the currently active cell down to cell 10.
Note: RowOffset represents the number of rows of area offset, positive numbers represent downward offsets, and negative numbers indicate upward offsets. The default value is 0.
6） Range("A1"). End(xlDown). Offset(1, 0). Select
Statement description: After executing the above code, select the first empty cell in the column (A1 is not empty)
Note: End (xlDown) refers to moving down
7） Range("A1"). End(xltoRight). Offset(0,1). Select
Statement description: After executing the above code, select the first empty cell in the row (A1 is not empty):
Note: End (xltoRight) is moved to the right.
8) ActiveCell.Offset(0, -ActiveCell.Column + 1). Select
Statement description: After executing the above code, move to the first cell of the current row (that is, the action you press the "Home" key)
ActiveCell.Offset( -ActiveCell.Row + 1.0). Select
Statement description: After executing the preceding code, move to the first cell of the current column.
Note: Make full use of the OFFSET function to implement cell selection.
9) ActiveCell.Offset(13, 14). Select
Selection.Offset(-3, -4). Select
Statement description: After executing the above code, the cell selection is moved.
Note: You can define a variable and implement it with offset,
For example: varFreightRowsCount = Range("A1"). CurrentRegion.Rows.Count
ActiveCell.Offset(varFreightRowsCount, 0). Select
The test code for this application:
Sub mynz_5() 'Lecture 5 Practical Use of the OFFSET Function in VBA
Sheets("5"). Select
'1）
Range("e4"). Select
'2)
Range(ActiveCell, ActiveCell.End(xlToLeft)). Select
'3）
Range(ActiveCell, ActiveCell.Offset(0, 3)). Select
'4）
Range(ActiveCell, ActiveCell.Offset(0, -3)). Select
'5）
Range(ActiveCell, ActiveCell.Offset(3, 0)). Select
'6）
Range("A1"). End(xlDown). Offset(1, 0). Select
'7）
Range("A1"). End(xlToRight). Offset(0, 1). Select
'8）
ActiveCell.Offset(0, -ActiveCell.Column + 1). Select
'9）
Range("a1"). Select
ActiveCell.Offset(13, 14). Select
End Sub
Code screenshot:
Today's content turn:
1What does the OFFSET function mean in VBA?
2What are the uses of the OFFSET function in VBA.
My 20+ years of practical experience with VBA are all condensed in the various tutorials below:
The 7th set of tutorials (three volumes): Excel Application of VBA: It is a basic explanation of VBA
The first set of tutorials (three volumes): "VBA Code Solution": is an improved tutorial after getting started
Tutorial 4 (16G): VBA Code Solution Video (Video Explanation of The First Set)
The third set of tutorials (two volumes): VBA Arrays and Dictionary Solutions: A special topic on arrays and dictionaries
The second set of tutorials (two volumes): VBA Database Solution: A special topic on databases
The 6th set of tutorials (two volumes): "VBA Information Acquisition and Processing": Explains the network and cross-program application of VBA
The 5th set of tutorials (two volumes): Interpretation and Utilization of Classes in VBA: Explanation of Classes and Interface Technologies
Tutorial 8 (three volumes): Word Application of VBA (Latest Tutorial): Utilization of VBA in Word
The sequence of learning of the above tutorials:
(1) 7→1→3→ 2→6→5 or 7→4→3→2→6→5.
② 7→8
How do I learn VBA? To sum up: in the process of learning, we must believe, understand, receive, and hold, and we must have the karma of returning. No matter what stage of learning you are at, you must bless your actual work with the knowledge points of the tutorial, and there will always be a huge harvest.