Help with VBA - Changing column headers?

[nerdygirl1225]

So I am trying to use this macro. Basically I want to sort column W ascending, then use the formula "=W2*-1" in column X, delete all values in column X that equal zero and W is blank, then copy and paste the values from column X to column I. When I do this it seems to work but it is changing the column header instead of starting in W2, X2, and I2... Can anyone help?

' Sort column W from smallest to largest
Range("$W$1:$W$" & Range("W" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("$W$2"), Order1:=xlAscending

' Add the formula "=W2*-1" to column X
Range("X2").Formula = "=W2*-1"
Range("X2").AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row)

' Delete values in column X where column Y is blank and X = 0
For a1 = 2 To Range("W" & Rows.Count).End(xlUp).Row
If IsEmpty(Range("W2")) And Range("X2") = 0 Then
Range("X" & a1).ClearContents
End If
Next a1

' Copy and paste values from column X to column I starting with X2
Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row).Copy
Range("I2").PasteSpecial xlPasteValues

 

[James006]

Hi,

To follow your thought process ... you can test following

Sub TestNerdy()
Dim i As Long

' Sort column W from smallest to largest
Range("$W$2:$W$" & Range("W" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("$W$2"), Order1:=xlAscending

' Add the formula "=W2*-1" to column X
Range("X2").Formula = "=W2*-1"
Range("X2").AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row)

' Your Sorting has placed all Blanks at the bottom
' and these cells are no longer taken into account

' Copy and paste values from column X to column I starting with X2
Range("I2:I" & Range("W" & Rows.Count).End(xlUp).Row).Value = Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row).Value

End Sub