CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Modify Excel VBA Procedure - clean cells in columns

I have created this procedure which works fine for cleaning blank cells in column AF. I want to modify this to pass on the column number as a variable so i can use the same procedure for other columns.

Column Z is my temp working column.

any ideas?

Private Sub clean_com_cells()
Dim counter As Integer, i As Integer, lastrow As Integer
lastrow = Mysheet.Range("AF65536").End(xlUp).Row
counter = 0
For i = 1 To lastrow
If Mysheet.Cells(i, 32).Value <> "" Then
Mysheet.Cells(counter + 1, 26).Value = Mysheet.Cells(i, 32).Value
counter = counter + 1
End If
Next i
Mysheet.Range("AF1:AF" & lastrow).Value = ""
Mysheet.Range("AF1:AF" & lastrow).Value = Mysheet.Range("Z1:Z" & lastrow).Value
Mysheet.Range("Z1:Z" & lastrow).Value = ""
End Sub

Upvotes: 1

Views: 622

Answers (3)

Will Ediger
Will Ediger

Reputation: 893

This is not how I would have done what you're trying to do, but this allows you to parameterize the column number that you're trying to "clear". I think that it would help to explain what you're trying to accomplish more explicitly, but this code should get you what you need. Note, you need lastrow to be a Long since Integers only go from -32k to 32k (approx). FYI, Longs perform better than Integers in recent versions of VBA, since Integers get converted to Longs. Never use Integers. Bytes, on the other hand, do perform better if your data fits that profile (0 to 255).

Private Sub clean_com_cells(column_number as integer)
Dim counter As Integer, i As Integer, lastrow As long
Dim clear_rng as range

lastrow = Mysheet.cells(65536,column_number).End(xlUp).Row
counter = 0

For i = 1 To lastrow
  If Mysheet.Cells(i, column_number).Value <> "" Then
    Mysheet.Cells(counter + 1, 26).Value = Mysheet.Cells(i, column_number).Value
    counter = counter + 1
  End If
Next i

with mysheet
  set clear_rng = Range(.cells(1,column_number), .cells(lastrow,column_number))
  clear_rng.Value = .Range("Z1:Z" & lastrow).Value
  Mysheet.Range("Z1:Z" & lastrow).Value = ""
end with

End Sub

Upvotes: 2

Dan Wagner
Dan Wagner

Reputation: 2713

You can adjust the function to take an input parameter, which I've named TargetColNumber below. I also added handy function for finding the last row in a worksheet... a future refactor might involve passing a Worksheet to the cleaning routine.

Anyway, call the function with a number and you should be good to...

Option Explicit
'this is the routine that cleans your cells
Sub clean_com_cells_in_col(TargetColNumber As Long)

Dim counter As Long, i As Long, lastrow As Long

Dim MySheet As Worksheet
Set MySheet = ThisWorkbook.ActiveSheet

lastrow = FindLastRow(MySheet)
counter = 0

For i = 1 To lastrow
    If MySheet.Cells(i, TargetColNumber).Value <> "" Then
        MySheet.Cells(counter + 1, 26).Value = MySheet.Cells(i, TargetColNumber).Value
        counter = counter + 1
    End If
Next i

With MySheet
    .Range(.Cells(1, TargetColNumber), .Cells(lastrow, TargetColNumber)).Value = ""
    .Range(.Cells(1, TargetColNumber), .Cells(lastrow, TargetColNumber)).Value = _
        .Range(.Cells(1, 26), .Cells(lastrow, 26)).Value
    .Range(.Cells(1, 26), .Cells(lastrow, 26)).Value = ""
End With

End Sub

'we'll use this function to identify the last row in a worksheet
Public Function FindLastRow(flrSheet As Worksheet) As Long
    If Application.WorksheetFunction.CountA(flrSheet.Cells) <> 0 Then
        FindLastRow = flrSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        FindLastRow = 1
    End If
End Function

'this is our test
Sub TestItYall()

Call clean_com_cells_in_col(4) '<~ did it work?

End Sub

Upvotes: 1

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

You can use a string variable for you column like you have done with lastrow. Example here uses a variable called "col": 

Private Sub clean_com_cells()
Dim counter As Integer, i As Integer, lastrow As Integer,col as string
col = "AF" 'Change this to vary the column
lastrow = Mysheet.Range(col & "65536").End(xlUp).Row
counter = 0
For i = 1 To lastrow
If Mysheet.Cells(i, 32).Value <> "" Then
Mysheet.Cells(counter + 1, 26).Value = Mysheet.Cells(i, 32).Value
counter = counter + 1
End If
Next i
Mysheet.Range(col & "1:" & col & lastrow).Value = ""
Mysheet.Range(col & "1:" & col & lastrow).Value = Mysheet.Range("Z1:Z" & lastrow).Value
Mysheet.Range("Z1:Z" & lastrow).Value = ""
End Sub

Upvotes: 0

Related Questions