Excel VBA Excel VBA Data Manipulation & Arrays 2 — Questions and Answers
Question 1: Which VBA method copies a range of cells to another location?
- Range.Transfer()
- Range.Move()
- Range.Copy() (Correct answer)
- Range.Clone()
Correct answer: Range.Copy()
Range.Copy() copies the specified range to the clipboard or directly to a destination range.
Question 2: How do you find the last used row in column A using VBA?
- Cells(Rows.Count, 1).End(xlUp).Row (Correct answer)
- Range("A1").LastRow
- Cells.LastRow(1)
- ActiveSheet.UsedRange.Rows
Correct answer: Cells(Rows.Count, 1).End(xlUp).Row
Cells(Rows.Count, 1).End(xlUp).Row navigates up from the bottom of column A to find the last non-empty row.
Question 3: Which VBA property returns the number of rows in a worksheet?
- Rows.Total
- ActiveSheet.RowCount
- Rows.Count (Correct answer)
- Sheet.MaxRows
Correct answer: Rows.Count
Rows.Count returns the total number of rows available in the active worksheet (1,048,576 in modern Excel).
Question 4: What does the Range.AutoFilter method do in VBA?
- Sorts the range alphabetically
- Applies or removes dropdown filter controls on a range (Correct answer)
- Fills missing values automatically
- Groups rows by value
Correct answer: Applies or removes dropdown filter controls on a range
Range.AutoFilter applies or toggles the AutoFilter dropdowns on the specified range, allowing row filtering.
Question 5: In VBA, which method deletes all content and formatting from a range?
- Range.Clear() (Correct answer)
- Range.Delete()
- Range.Erase()
- Range.Reset()
Correct answer: Range.Clear()
Range.Clear() removes both the content and the formatting of all cells in the specified range.
Question 6: What does the Transpose function do when used with VBA Range data?
- Sorts data in reverse order
- Converts rows to columns and columns to rows (Correct answer)
- Removes duplicate values
- Formats data as a table
Correct answer: Converts rows to columns and columns to rows
Transpose switches the orientation of an array or range, turning rows into columns and vice versa.
Excel VBA Excel VBA Data Manipulation & Arrays 2
Which VBA method copies a range of cells to another location?