Loop in Array

**stanleydgromjr** · 07-22-2011, 04:11 AM

Team,

I am finally moving from manipulating data in worksheets to working with arrays.

Can someone suggest an alternative to to the BOLD array loop? Is there a way to change AH(a,2) thru Ah(a,8) to zeros without looping?

Code:

Option Explicit
Sub ChangeRows()
Dim AH, a As Long, aa As Long
AH = Cells.Cells(1).CurrentRegion.Resize(, 8)
For a = LBound(AH) To UBound(AH)
  If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then

    For aa = 2 To 8
      AH(a, aa) = 0
    Next aa

  End If
Next a
Cells.Cells(1).CurrentRegion.Resize(, 8) = AH
End Sub

Thanks in advance.

Have a great day,
Stan

**Admin** · 07-22-2011, 08:44 PM

Hi Stan,

Welcome to ExcelFox !!

I don't think it's possible. I might be wrong though.

You could try this, which uses less loop

Code:

Sub ChangeRows()
    
    Dim AH, a As Long, aa As Long
    Dim r As Range, ka() As String, k  As String, n As Long
    
    With Range("a1")
        AH = .CurrentRegion.Resize(, 8)
        Set r = .Cells(1, 2).Resize(.CurrentRegion.Rows.Count, 7)
    End With
    
    For a = LBound(AH) To UBound(AH)
      If AH(a, 1) = "DEF" Or AH(a, 1) = "GHI" Then
            n = n + 1
            ReDim Preserve ka(1 To n)
            ka(n) = a
      End If
    Next a
    Cells.Cells(1).CurrentRegion.Resize(, 8) = AH
    If n Then
        With r
            For a = 1 To n
                .Rows(ka(a)).Value = 0
            Next
        End With
    End If

End Sub

**stanleydgromjr** · 07-22-2011, 10:44 PM

Admin,

Thank you for the welcome to ExcelFox.

I tried your code, and it did give a different approach to the problem - results were the same.

This is a keeper for my archives.

Thank you very much.

Have a great day, and weekend,
Stan

**Admin** · 07-22-2011, 10:50 PM

Hi Stan,

You are welcome !

Have a great weekend to you as well

**Rasm** · 07-28-2011, 04:49 AM

You can maybe use some of these comands-These are just tings to consider - hope they give you pointers

Code:

Option Explicit 'forces you to declare variables
Option base 1 'All arrays are diminsioned using base 1 rather than zero - in many cases Excel uses base 1 when creating arrays

Dim ah() AS variant 'By leaving the parentesies empty - you can use the Redim later

'Now use
Redim ah(1 to NumRows, 1 to NumColumns) 'Makes it a specific size -or use it with Preserve to simply change the size of the array - without losing current values.

Erase ah ' will delete entire array