Help with VBA modification

[Yamasaki450]

Hello.

I need some help to modify this VBA... Right now this VBA only works in "Sheet1". I would like it to work in all 8 sheets in my workbook at once... So in "Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6, Sheet7, Sheet8"

Is this possible?

Here is VBA i use

Option Explicit
Sub Delete_White_Cells_And_Shift_Up()
    Dim t As Double: t = Timer
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<-- *** Change sheet name to suit ***
    Dim r As Range
    Set r = ws.Range("L1627:VQX1627").CurrentRegion
    
    Dim a, b
    a = r
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    Dim i As Long, j As Long, k As Long, LRow As Long, LCol As Long
    LRow = r.Rows.Count
    LCol = r.Columns.Count
    k = 1
    For j = 1 To LCol
        For i = 1 To LRow
            If r.Cells(i, j).DisplayFormat.Interior.Color <> RGB(255, 255, 255) Then
                b(k, j) = a(i, j)
                k = k + 1
            End If
        Next i
        k = 1
    Next j
    ws.Range("L1627:VQX1627").Resize(LRow, LCol).Value = b
    MsgBox Timer - t
End Sub

 

[Burrgogi]

This also works i just didnt know where to put this line exactly.

Just so you know for future reference, in VBA any line that starts with an apostrophe is regarded as a comment and is not executed. If you look at his posting again, you can see his comment to you on where your code should have been placed. If he had the arrow pointing the opposite way, it would have made more sense I think but that's just a minor little nitpick.