Convert entire (last name) column to lowercase and remove spaces

Question

The code does that it is supposed to do but it should also work when I add a person to the list. So the problem is in the first block

I tried changing the 3th line to xldown but this does not work.

Selection.AutoFill Destination:=Range("C2:xlDown")

I tried Googling the solution but only got more confused. The explaination of what the code does is below the code.

Full code:

Sub btn_SortLastName()

'Add content of column B into colum C in lowercase
   Range("C2").Select
   ActiveCell.FormulaR1C1 = "=LOWER(RC[-1])"
   Selection.AutoFill Destination:=Range("C2:C26")


'Copy selection
   Range("C2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy

'Paste selection without formating and remove spaces
   Selection.PasteSpecial Paste:=xlPasteValues
   Selection.Replace What:=" ", Replacement:=""

'Sort in decending order
   Range("C1") = "Index"
   Columns("A:C").Sort key1:=Range("C2"), _
   order1:=xlAscending, Header:=xlYes

'Hide column C and set title on C1
   Columns("C").Select
   Selection.EntireColumn.Hidden = True
   Range("C1").Value = "Hidden"

End Sub

In Dutch there are a lot of last names that are separated by spaces, I want to remove the spaces copy them into a hidden column and transform them into lowercase and sort them.

Example:

B2:De Wolf    C2:dewolf
B3:De Bisscop C3:debisscop

Replace `Selection.AutoFill Destination:=Range("C2:C26")` with `Selection.AutoFill Destination:=Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)`. Also, check [avoid Select](https://stackoverflow.com/a/10717999/9199828) — Foxfire And Burns And Burns, Feb 12 '19 at 12:25
I replied too soon, this also works! Thanks for that! I think I'll continue with SJR's code and compare it to mine to improve in the future. — Denisuu, Feb 12 '19 at 12:36

score 3 · Accepted Answer · edited Feb 12 '19 at 13:15

3

You should read up on how to avoid select.

Try this

Sub btn_SortLastName()

With Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=LOWER(RC[-1])"
    .Value = .Value
    .Replace What:=" ", Replacement:=""
End With

Range("C1").Value = "Hidden"
Columns("A:C").Sort key1:=Range("C2"), order1:=xlAscending, Header:=xlYes
Columns("C").EntireColumn.Hidden = True


End Sub

edited Feb 12 '19 at 13:15

Denisuu

43
1
8

answered Feb 12 '19 at 12:23

SJR

22,986
6
18
26

1

`Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row` Good one there! I updated my comment thanks to this line, because I did not notice that probably column C would be empty and is better to get last row of B. Good shot! – Foxfire And Burns And Burns Feb 12 '19 at 12:27
Thank you! I'll mark this as the answer! Your code is so much cleaner than mine! I'm quite new to VBA so I just use macro's and then delete what I don't need or don't understand. – Denisuu Feb 12 '19 at 12:33
1

Very nice use of `With` ! – Gary's Student Feb 12 '19 at 12:47

Convert entire (last name) column to lowercase and remove spaces

1 Answers1