Do you have any Excel OCD habits?

[Sandeep Warrier]

People who insist on selecting the whole worksheet before they do a sort or apply a filter!!!

Dom

Worse than that it when people leave out a couple of column headers so quick sort only sorts half the data.

Nick

And leave blank rows in data so auto-filter doesn't filter all data.

 

[Jon von der Heyden]

And leave blank rows in data so auto-filter doesn't filter all data.

I think that there are aot of people that don't quite undetstand how autofilter works. Many select only the header row (e.g. 1:1) and then invoke Auto Filter. With this method excel will attempt to determine the filter range and it will only grab the first area / contiguous range (i.e. last row to the 1st blank row).

One should always select the actual table (e.g. A1:Z100) rather than just the header row, thus explicitly telling excel what the filter range is.

 

[litrelord]

One should always select the actual table (e.g. A1:Z100) rather than just the header row, thus explicitly telling excel what the filter range is.

True, but if it's a table of data for analysis rather than presentation it should be kept as one contiguous range anyway. I prefer Alt, D, F, F over making sure I select a range first (haven't progressed past using Excel 2003 key combinations yet for most things).

The introduction of 'Tables' in 2007 was a big help when it came to this, saved a lot of dynamic named ranges for me too.

Nick

 

[Domski]

One should always select the actual table (e.g. A1:Z100) rather than just the header row, thus explicitly telling excel what the filter range is.

I would never do this unless I've got multiple header rows and Excel guesses the wrong one, or the data layout I'm working with is a bit 'interesting'. I wouldn't select the header row either, just a cell in the data table.

Dom

 

[MrKowz]

Another of mine I just noticed today, I put an extra line in my VBA code after I Dim my variables, after I define my variables, and before/after each parent For, If, and With trees. I find it easier to read and follow.

Example (This is code I wrote for someone a month or two ago):

Public Sub CutDate()
Dim i As Long, rowx As Long, LR As Long, LC As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
rowx = 1
LR = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
LC = Sheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column
 
For i = LR To 2 Step -1
    If Sheets("Sheet1").Range("G" & i).Value > DateSerial(2011, 1, 20) Then
        Sheets("Sheet1").Rows(i).Cut Destination:=Sheets("Sheet2").Range("A" & rowx)
        Sheets("Sheet1").Rows(i).Delete
        rowx = rowx + 1
    End If
Next i
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[Sandeep Warrier]

I think that there are aot of people that don't quite undetstand how autofilter works. Many select only the header row (e.g. 1:1) and then invoke Auto Filter. With this method excel will attempt to determine the filter range and it will only grab the first area / contiguous range (i.e. last row to the 1st blank row).

One should always select the actual table (e.g. A1:Z100) rather than just the header row, thus explicitly telling excel what the filter range is.

True. I usually use Ctrl+A to select data and a blank row doesn't let me do that too....

 

[Greg Truby]

I tried, I really tried to walk away. But Keith has pushed one of my MrExcel OCD buttons.

There is always a chance that you will some day take code from an existing routine and copy and paste it into another project where the original routine gets called by a "Main" routine, i.e. the original procedure is now a part of a larger sequence of procedures. You don't want to assume that you are restoring the settings to their original values when the routine was called, you want to know that you're restoring them. In other words.

...
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
...
With Application
    .ScreenUpdating = False      '// <=== hey, I just copied and pasted it, doesn't mean I understood it. :-Þ
    .Calculation = xlCalculationAutomatic
End With

should read

Dim i As Long, rowx As Long, LR As Long, LC As Long, _
    booScrnUpdt As Boolean, lngCalcMode As XlCalculation
 
With Application
    Let booScrnUpdt = .ScreenUpdating
    Let lngCalcMode = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
'// {snip}
 
With Application
    .ScreenUpdating = booScrnUpdt
    .Calculation = lngCalcMode
End With

 

[MrKowz]

I tried, I really tried to walk away. But Keith has pushed one of my MrExcel OCD buttons.

There is always a chance that you will some day take code from an existing routine and copy and paste it into another project where the original routine gets called by a "Main" routine, i.e. the original procedure is now a part of a larger sequence of procedures. You don't want to assume that you are restoring the settings to their original values when the routine was called, you want to know that you're restoring them. In other words.


should read

Dim i As Long, rowx As Long, LR As Long, LC As Long, _
    booScrnUpdt As Boolean, lngCalcMode As XlCalculation
 
With Application
    Let booScrnUpdt = .ScreenUpdating
    Let lngCalcMode = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
'// {snip}
 
With Application
    .ScreenUpdating = booScrnUpdt
    .Calculation = lngCalcMode
End With

I like! Never really thought about handling it in that fashion

Now lets see what other buttons I can find!

 

[RoryA]

Those of us born after 1900 can, of course, omit the use of the word 'Let'...

 

[MrKowz]

Those of us born after 1900 can, of course, omit the use of the word 'Let'...

According to the help file: "Explicit use of the Let keyword is a matter of style"

Maybe Greg just likes to code with style!