Loop in Array

[stanleydgromjr]

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?





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]

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


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]

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]

Hi Stan,

You are welcome !

Have a great weekend to you as well :cheers:

[Rasm]

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




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

[stanleydgromjr]

Rasm,

More good information for my archives.

Thank you for responding.

Have a great day,
Stan