Limiting Formula to reduce unnecessary calculations

A

Audi_780

New member
Joined
Oct 25, 2016
Messages
4
Reaction score
0
Points
0
Hello,

I'm just starting to understand a little bit of visual basic, and need a bit of assistance with making this formula run much faster and not do thousands of unnecessary calculations.

Here's the code I currently have:

Code: 
Sub Insert_Len()
'Inserting a Column at Column AS
Range("AS1").EntireColumn.Insert
'Inserting a formula at Column AS
Range("AS:AS").Formula = "=LEN(K:K)"
End Sub

My spreadsheet contains anywhere from 100 rows to 20,000 but when running this formula I'm ending up with over 50,000 cells with 0 value which are not needed. So first question is how to make the formula stop when the entire row is blank as I do have some values in K column which are blank but i don't want the formula to stop there.

Any assistance would be greatly appreciated, and I hope to receive comments on the lines so I can understand what each line is for.
 
Last edited by a moderator:
On a sheet where you haven't previously run your code (because your code makes the all the rows on the sheet part of the used range), try:
Code: 
Sub Insert_Len()
'Inserting a Column at Column AS
Range("AS1").EntireColumn.Insert
'Inserting a formula at Column AS
Intersect(ActiveSheet.UsedRange.EntireRow, Range("AS:AS")).FormulaR1C1 = "=LEN(RC[-34])"
End Sub
 
Last edited:
p45cal said:
On a sheet where you haven't previously run your code (because your code makes the all the rows on the sheet part of the used range), try:
Code: 
Sub Insert_Len()
'Inserting a Column at Column AS
Range("AS1").EntireColumn.Insert
'Inserting a formula at Column AS
Intersect(ActiveSheet.UsedRange.EntireRow, Range("AS:AS")).FormulaR1C1 = "=LEN(RC[-34])"
End Sub
Click to expand...

Thank you very much, worked like a charm and entire macro ran in less than 10 seconds, for my second questions if anyone is able to help. I'm trying to delete raws with value for column K that does not equal to 6. The way I currently have it is that comun K gets filtered to show only values that equal to 6, the problem with that is when I save the file as CSV my fitler doesn't save so i end up having to filter it again after every time i open the spreadsheet. Here's the macro I have so far:


Code: 
Sub Clean_Filter()
' Clean Macro
Call Insert_Len
Call Basic_Filter

Range("A:I,L:O,Q:Q,U:V,X:X,Z:AF,AH:AL,AN:AR").Select
Selection.Delete Shift:=xlToLeft
Columns("K").Hidden = True

Call Filter_Date
End Sub

Sub Basic_Filter()
' Basic Macro
With Sheet1
  Range("A1:BB20000").AutoFilter _
      Field:=45, _
      Criteria1:=Array("6"), _
      Operator:=xlFilterValues
End With
End Sub


I read online and tried to do it with multiple different examples available in this forumn but I couldn't figure out how to use the Dim command properly to make it work.


Any suggestions would be greatlty appreciated!
 
Last edited by a moderator:
Hi
please don't quote entire posts unnecessarily. They are just clutter and make the thread hard to read.

Also please wrap code with code tags ( Go Advanced - Select code - Click #

Thank you
 
Audi_780 said:
I'm trying to delete raws with value for column K that does not equal to 6. The way I currently have it is that comun K gets filtered to show only values that equal to 6, the problem with that is when I save the file as CSV my fitler doesn't save so i end up having to filter it again after every time i open the spreadsheet.
Click to expand...
This will delete non-6s:
Code: 
Sub Basic_Filter()
' Basic Macro
With Sheet1
  .Range("A1:BB20000").AutoFilter Field:=45, Criteria1:="<>6", Operator:=xlFilterValues
  Intersect(.AutoFilter.Range, .AutoFilter.Range.Offset(1)).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
  .AutoFilter.Range.AutoFilter
End With
End Sub
 
Thanks man! I feel bad awkward, I've never asked someone for help before. Specially not someone I don't know but I really appreciate it.

I noticed in the script above I've been assuming my rows are 20000, I know my columns are correct since it's constant, any chance we can use Len to save some time on calculations in case I have less than 20000 rows?
 
I haven't checked, but as long as there are no completely blank rows in the columns A:BB among your data I think you could use:

.Range("A1").AutoFilter~~~
instead of:
.Range("A1:BB20000").AutoFilter~~~
Apart from that, is there a single column that you can say will always have data in every row? We could use that column length at the start to set a last row value that could be used throughout the relevant macros.
 
Last edited:
That's smart, I think I've used something similar to that when writing a program in C language back in university. I'll give it a shot when I get home and see I can figure out the Marco and how to call it
 
You must log in or register to reply here.
Back
Top