Nick's guide to Excel / VBA Interview Questions
I have hosted hundreds of Excel / VBA interviews, and I can work out very quickly how good someone is.
The biggest blunder to make in an interview is to say something like: "Oh, that's easy, I can do that in 5 minutes with the compiler and help files"
Well, in my interview, you have no compiler and no help files, so don't ignore syntax, or rely too much on help files.
You wouldn't turn up to a French interview with a dictionary and translate every word now would you ?
; - >
Here are some Excel VBA interview questions I might ask you.. Add a comment if you think you have a good answer.
»
- Nick's blog
- Login or register to post comments
- 136086 reads
How about this solution
Private Sub DiagSelect()
Dim colCount As Long
'Get the last column populated with data
colCount = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Range("A1", ActiveSheet.Cells(ActiveSheet.Rows.CountLarge, colCount).End(xlUp).Address).Select
End Sub
another solution
Sub Diagonal()
Dim i As Integer, j As Integer
' take advantage of fact that we have only 256 columns (at least up to 2003 :))
Do While (Cells(256 - i, 256 - j) = "")
i = i + 1
j = j + 1
Loop
Range(Cells(1, 1), Cells(256 - i, 256 - j)).Select
End Sub
My solution
Sub Diagonal()
Dim Rowcount, ColumnCount As Long
Rowcount = ActiveSheet.UsedRange.Rows.Count
ColumnCount = ActiveSheet.UsedRange.Columns.Count
Dim i As Long
i = WorksheetFunction.Max(Rowcount, ColumnCount)
Do While ActiveSheet.Cells(i, i) = ""
i = i - 1
Loop
Cells(1, 1).Resize(i, i).Select
End Sub
Re: VBA Range Selection Question
Hope the below code will answer your question:
Sub GetDiagonal()
Dim lLRow As Double
Dim lLCol As Double
ActiveSheet.Cells(1, 1).Select
With ActiveSheet.UsedRange
ActiveCell.SpecialCells(xlLastCell).Select
lLCol = Selection.Column
lLRow = Selection.End(xlUp).Row
End With
ActiveSheet.Range(Cells(1, 1), Cells(lLRow, lLCol)).Select
End Sub
-Boss-
-Excel Student-
Re: VBA Range Selection Question
The Above code submited by me.
-boss-
-Excel Student-
how about this?
Sub selectdiagonal()
Cells(1, 1).Select
i = 1
While Cells(i, i) <> ""
i = i + 1
Wend
i = i - 1
Cells(i, i).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
End Sub
Efficient diagonal selection
With Sheets(1)
Set f = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
.Range(.Range("A1"), .Cells(f.Row, f.Column)).Select
End With
select diagonal range
Public Sub selectDiagonal()
Dim c As Range, rDiagonal As Range
Dim i As Long
Set c = ActiveSheet.[b2]
While Not c.Column = Sheet1.Columns.Count
If LenB(c.Value2) <> 0 Then Set rDiagonal = c
Set c = c.Offset(1, 1)
Wend
ActiveSheet.Range([a1], rDiagonal).Select
Set c = Nothing
Set rDiagonal = Nothing
End Sub
My first try in VBA
Sub SelDiag()
ActiveSheet.Range("a1").Select
Do Until Selection.Value = Empty
Selection.Offset(1, 1).Select
Loop
DiagLen = Selection.Column - 2
ActiveSheet.Range("a1", Range("a1").Offset(DiagLen, DiagLen)).Select
End Sub
Excellent 1st try
...good effort.
Have a look at the other solution posted recently..
What most people omit are the following: