Copying lists into columns of a table

Edit: I found a potential solution. Changing all of the Dim as Long to Dim as Integer allows the scripts to run smoother. However, there is a potential that the number of rows in some instances will exceed 32767. Is there a way I can put a condition on the first few statements, to use integer only if there are less than 30k rows?

The following script works to take a large dataset from this form in the Worksheet "List"

1    David
Eve
Freya
Sam
Yarra
2    Brian
David
Eva
Harry
Paul
3    Charlie
David
4    Eva
Harry
Paul

And paste it into another worksheet, "Library," like this:

1          2          3           4
David      Brian      Charlie     Eva
Eve        David      David       Harry
Freya      Eva                    Paul
Sam        Harry
Yarra      Paul

While generating a singleton list of one of each name in the worksheet "List".

The (long) code is pasted below. As I said before, it works fine with shorter lists, but not with larger ones (over 20,000), as it gives me a "Not responding" message where I'm forced to End Task. Is there anything else I can do to fix this? It has been suggested that I add DoEvents, but I'm not sure how I could implement this into the current script. Also, it taking long wouldn't bother me too much if I could have some sort of Progress Bar that updates at each step. Would that work as a DoEvent? All suggestions are welcome.

```
Sub RunAH_KeyWordLibrary()
Call PrepareKeywords
Call PrepareLibrary
End Sub

Private Sub PrepareKeywords()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Dim Library As Worksheet
Dim List As Worksheet
Set Library = Sheets("Library")
Set List = Sheets("List")

Dim Rng As Range
Dim i As Long
Dim lastRow As Long
i = 1
lastRow = Library.Range("A1").SpecialCells(xlCellTypeLastCell).row

While i 0 Then
Rng.Delete Shif

Mat has covered most everything. But,

In RemoveDuplicates and I'll assume List is a codename (or not declared) for a sheet, why not do the same for Library?

Worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet.

If IsNumeric(Rng.Value) = True And Len(Rng.Value) > 0 Then

Doing something like If method = True then is redundant, you can just say If method then

Things like

And IsEmpty(Rng.Value) = False

are better understood by structuring them like this

And Not IsEmpty(Rng.Value) Then

I do want to say good job on (declaring and) giving variables meaningful names and following Standard VBA naming conventions.

StackExchange Code Review Q#128490, answer score: 4