EXCEL: Fastest way to fill 7000 cells with a formula? 4

[VBAjedi]

Hi all!

I need a fast way to fill 7000+ cells with a formula that refers to that cell's row. Row one would be "=SUM(N1:AK1)"

OR

I need a way to check each of 7000+ rows for a value greater than 0 in any of 8 columns.

If I use the first method, I will just delete the formulas after my check anyway. So the second method might be better. So far I've tried (TempRange1 = AO10:AO7000):

For Each TempRange2 In TempRange1
   TempRange2.Value = "=SUM(N" & TempRange2.row _
       & ":AK" & TempRange2.row & ")"
Next TempRange2

but it is VERY slow.


VBAjedi

 

[SteveBausch]

Okay, you have supplied a relative reference formula.

Please define your 7000 cells. If you give the range(s), I can craft your VBA code for you.

Pretty simple, something like:


Range(cells(1,1),cells(7000,1)).formula = "=SUM(N1:AK1)"
OR
Range(cells(1,1),cells(3500,2)).formula = "=SUM(N1:AK1)"

 

[VBAjedi]

Steve,

The sample formula I gave is what I'm after, but it must refer to THAT CELL'S (the one I'm placing the formula in) row. So the formula for each cell is different (as my code snippet illustrates).

I'd prefer a way to check columns N-AK of each row for non-zero values without having to write the formula to the sheet (it's just going to get deleted a few seconds later anyway).

Thanks!




VBAjedi

 

[Zathras]

...I need a way to check each of 7000+ rows for a value greater than 0 in any of 8 columns.
...

Columns N to AK implies 24 columns, not 8.

What kind of output do you want? A simple MsgBox with "Ok" or "Error"? Counts of non-zero in a particular column? (Which column?)

Specific rows? All rows in used range? Excluding a header row?

You're not giving us much to work with here.

 

[SteveBausch]

Funny about Excel, since you provided a relative formula, it will automatically adjust the formula for each cell.

Run the code, take a look. I did.

 

[VBAjedi]

Steve,

I stand corrected! (Happens alot LOL) I get nervous trusting Excel to automatically adjust for me (I'm always writing code that interferes with it's automated functions). But your code does work. A star for that!

Zathras,

You're right! I was in a hurry and hoped I could just sketch out a general picture to get some input.

All I'm trying to do is find a quick way to scan all the rows in SummaryRange to see if columns N-AK (yes, thats 24 columns LOL) contain number values greater than zero and delete them if they don't. It also needs to update SummaryRange, LastSummaryRow, and DeletedRecordsCounter. Right now my code pastes a temporary formula to spreadsheet column AO:

Set TempRange1 = shSS.Range("AO" & SummaryRange.Cells(1, 1).row & ":AO" & LastSummaryRow)
TempRange1.Formula = "=SUM(N" & SummaryRange.Cells(1, 1).row & ":AK" & SummaryRange.Cells(1, 1).row & ")"

For Each c In SummaryRange
   x = SummaryRange.Rows.Count + SummaryRange.Cells(1, 1).row - 1
   x = c.row
   Application.StatusBar = "Now scanning row " & c.row & " for zero balances."
   Do While shSS.Range(&quot;AO&quot; & x).Value = 0 And x < LastSummaryRow
      shSS.Rows(x & &quot;:&quot; & x).Delete
      DeletedRecordsCounter = DeletedRecordsCounter + 1
      LastSummaryRow = shSS.Cells.Find(what:=&quot;*&quot;, after:=Range(&quot;A1&quot;), _
       searchorder:=xlByRows, searchdirection:=xlPrevious).row
      Set SummaryRange = shSS.Range(&quot;I13:I&quot; & LastSummaryRow)
   Loop
Next c

Works, but it's slow and UGLY!


VBAjedi

 

[Zathras]

I can't quite grasp what exactly you are trying to do.

Do you have a range named "SummaryRange" on the worksheet?

Are you merely trying to delete rows that have all zeros in columns N thru AK? (Regardless of what may be in other columns on the same row?)

Would you want a function that looks like this:

  nDeletedRows = DeleteZeroRows( "SumaryRange" )

function DeleteZeroRows( ARangeName as string ) as long
Dim nWorkingRange as Range
Dim nCountDeletes as Long
  Set nWorkingRange = Range(ARangeName)
 (etc.)
  DeleteZeroRows = nCountDeletes
end function

 

[VBAjedi]

Zathras,

Yes, my spreadsheet has a large range on it named SummaryRange. Starting with the first row of this range, and ending with the last row of the range, I want to "delete rows that have all zeros in columns N thru AK. (Regardless of what may be in other columns on the same row)" - just like you stated. In the process I need to update SummaryRange, LastSummaryRow, and DeletedRecordsCounter to reflect the deletions (although in theory SummaryRange should update itself to reflect deleted rows).

I used a "Do while. . ." loop to account for the possibility of having two consecutive rows with zero values. I can't use a "For. . . Next" loop to simply step through the rows because it would skip the second consecutive zero-value row (it would shift the row up into a row number that the loop would have already processed).



VBAjedi

 

[Zathras]

Try this:
I took a shortcut. Given that the values will always be non-negative it is sufficient to test a sum for zero. In the case where there could be + and - offsetting values (netting to zero), then a slightly different technique would be needed, but the approach would be essentially the same.

Option Explicit

Sub test()
Dim nDeletedRows
  nDeletedRows = DeleteZeroRows("SummaryRange")
  MsgBox nDeletedRows & " rows deleted.  New range = " & Range("SummaryRange").Address
End Sub

Function DeleteZeroRows(ARangeName As String) As Long
Dim rWorkingRange As Range
Dim rTestRange As Range
Dim nCountDeletes As Long
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nFirstCol As Integer
Dim nLastCol As Integer
Dim nRow As Long
Dim nNewLastRow As Long

  'Remember where we started

  Set rWorkingRange = ActiveSheet.Range(ARangeName)
  nFirstRow = rWorkingRange.Row
  nLastRow = rWorkingRange.Rows.Count + nFirstRow - 1
  nFirstCol = rWorkingRange.Column
  nLastCol = rWorkingRange.Columns.Count + nFirstCol - 1

  'Scan rows and delete where all zero

  For nRow = nLastRow To nFirstRow Step -1
    Application.StatusBar = "Processing row " & nRow
    Set rTestRange = Range(Cells(nRow, nFirstCol), Cells(nRow, nLastCol))
    If WorksheetFunction.Sum(rTestRange) = 0 Then
      rTestRange.EntireRow.Delete
      nCountDeletes = nCountDeletes + 1
    End If
  Next nRow

  'Re-set range definition

  nNewLastRow = nLastRow - nCountDeletes
  Application.Names(ARangeName).Delete
  Application.Names.Add ARangeName, _
           Range(Cells(nFirstRow, nFirstCol), Cells(nNewLastRow, nLastCol))

  'Wrap it up

  Application.StatusBar = False
  DeleteZeroRows = nCountDeletes
  Set rTestRange = Nothing
  Set rWorkingRange = Nothing
End Function

I find problems like this much easier to solve if they can be encapsulated in a function or subroutine with just a couple of input/output parameters.

Your analysis about using For..Next when deleting rows is correct as far as it goes, however it is incomplete. The work-around is to start at the bottom and work up ("step -1&quot.

 

[xlbo]

and a little BTW
For x = a to b step y
may not be quite as fast as
for each next
BUT it is waaaaay more flexible - the step can be ANYTHING - positive, negative, decimal (although not really useful for row looping)
This is very useful for deletes cuz you can use the step -1 as Zathras suggested (just remember to start from the bottom when you use negative steps)
It is also very useful where you have repeated blocks of data
You can use an exterior loop to increment in large steps (say of 50 ) and then use an interior loop to do the work within that block, relative to the exterior loop.

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[VBAjedi]

Thanks, Zathras and Geoff! Stars for your contributions. The "Step - 1" concept is one of those "Duh - I should have thought of that!" things but it's very powerful.

Zathras, thanks also for the code you offered. I didn't know about "WorksheetFunction". It was cut-and-paste perfect and ran correctly, but as it turns out, my code actually runs faster in my application. Perhaps because I don't split it out into a function??? In your opinion, what's the advantage of using a function for code that will only be used by one sub?

Thanks!


VBAjedi

 

[Zathras]

....what's the advantage of using a function for code that will only be used by one sub?
...

The way I tackle a problem is to break it into smaller pieces and solve the smaller problems in turn. I try to put the pieces into their own "bubbles" with limited interaction thru formal parameter lists. Sometimes the function/sub can be reused somewhere else which is an added bonus, but the main thing is being able to make changes 6 or 12 months from now. I always code with an eye to future maintenance. After the code is no longer fresh in your mind, it is easier to make a change in one place without breaking the code someplace else when there are smaller pieces to work with and when formal parameters are used.

One place to break is when the "level" changes. Another is when the code involves a loop. For example if I am going to do a lot of stuff on each sheet in a workbook, I would probably put the looping stuff in one routine and call a separate routine to do the stuff in one sheet (for each sheet). I sort of get lost with more than 3 or for nested loops, so I break it down with separate subs, one loop per sub.

Another way to look at it is to divide the code between controlling code and processing code. Sort of like between managers and workers. Put the decision making stuff in one sub and then call a worker sub to do stuff unconditionally.

A function should be defined based on the job it is required to do, not just the type of code in it. I have written many programs that have only one line of executable code in a subroutine or function. The function has a clear purpose with clear inputs and output. If I can use tricky code to implement it, fine. But there is a difference between definition and implementation.

You asked...

 

[VBAjedi]

Zowie, Zathras! Good stuff, that. Sounds like the voice of experience (read: "mistakes&quot. LOL Another star. . .

Thanks!


VBAjedi

 

[SteveBausch]

I agree with Zathras on "one line of code" in a sub/function.

A properly named routine is self-documenting. So, instead of "Why did I have this in here?", you can have a routine named "KeepFromCrashing".