0

I have a giant macro. Because the macro needs to be applied to datasets ranging for 1 to 200,000 rows, there are a lot of steps where I use an =IF formula where one of the outputs is "". Ex: 

=IF(B2="","",CONCATENATE(B2,C2)

These formulas are then autofilled to the bottom of the worksheet. Since "" is not a true blank, the files at the end of the output end up being at least 80 mb each, when some could be 10kb.

I can trim the file again if I export to CSV, change the file extension to txt, open in notepad, and delete all of the rows consisting of nothing but commas, then re-importing the txt file back into Excel, but that requires me to do the extension changing and comma deleting manually, which gets very time consuming.

Does anyone have any tricks (like a substitute for "" that leave behind a true blank after a paste values, or a command that can delete all rows where A = "" that wont take 16 hours to run, evaluating and deleting each of the 1.54 million rows individually)?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2989297
  • 173
  • 1
  • 1
  • 9
  • Do a filter on column A for blanks then delete? Depending on how many rows and your memory, could still take minutes but considerably less than 16 hours XD – findwindow Sep 08 '15 at 20:58
  • Okay, so I think I could work out a filter, but then what is the VBA command to select all non-hidden cells? Even if I filter first, If I say "Select all", wont it delete everything? – user2989297 Sep 08 '15 at 21:03
  • [To select just the visible cells from a range of selected cells, you can use the following line of code: `Selection.SpecialCells(xlCellTypeVisible).Select`](http://excelribbon.tips.net/T008524_Selecting_Visible_Cells_in_a_Macro.html) – BruceWayne Sep 08 '15 at 21:04
  • why do you always have to fill to the bottom of the sheet? Can't you just fill down as far as the data? – aucuparia Sep 08 '15 at 21:05
  • Filter the column on blanks and then delete it. See [This](http://stackoverflow.com/questions/11317172/delete-row-based-on-condition/11317372#11317372) Simply use the criteria as `Criteria1:="="` for blanks – Siddharth Rout Sep 08 '15 at 21:09
  • @aucuparia If you can do that, I do not know how. I looked and did not find a method to make an autofill automatically know how many rows of data there actually are. – user2989297 Sep 08 '15 at 21:12
  • are you using VBA to fill in the formulae? – aucuparia Sep 08 '15 at 21:13
  • Yes, VBA is doing everything. Im taking a command line DIR export and trying to format that in a way that is conducive for non-technical resources to work with. There are just under 100 transformations in the process to get to the file format that is being asked for. Needed to macro it so it took 45 minutes instead of 2 days to do a file. – user2989297 Sep 08 '15 at 21:14

1 Answers1

0

Something like this should do it in one go:

Range("E2:E" & Range("A" & Rows.count).end(xlup).row).formula = "=IF(B2="""","""",CONCATENATE(B2,C2)"

I have made the following assumptions:

You want the formula in column E, change here if not: Range("E2:E"

Column A has data all the way down to where you want the formula, change here if not: & Range("A"

Excel is smart enough to increment this formula as it posts it to a range, no need to fill down after filling in just one cell.

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Just to round back, you are my hero. I marked as answered because the theory made sense to me, but I just got around to trying to put it into practice. This is EXACTLY what I needed. I knew Excel was smart enough to do it. I also knew I wasnt :) – user2989297 Sep 09 '15 at 23:48