0

A report that I pull is horribly setup, and my attempts to fix it are not going well.

I've gotten a good amount of it done, but unmerging all the cells in the used range is rather difficult, because, for some reason, when the report is converted, it reports itself as having 29,000 rows of data (there's really on ~850).

My workaround was to simply pull the entiresheet into an array, thinking that all of the cells will be unmerged / played correctly. Instead, the array seems to be cutting out entire columns of data.

Is there an efficient way to remedy this?

Please find my current code below.

If wbLoop.Name = "Report.xlsx" Then

        If wbLoop.Sheets(1).Cells(1, 1).Value2 = "" Then wbLoop.Sheets(1).Cells(1, 1).EntireColumn.Delete

        Do Until wbLoop.Sheets(1).Cells(1, 1).Value2 = "Finance1"
            wbLoop.Sheets(1).Cells(1, 1).EntireRow.Delete
        Loop

        arrReport() = wbLoop.Worksheets("Report").UsedRange.Value2
Gregory
  • 315
  • 1
  • 3
  • 13
  • Without seeing the source worksheet it's difficult to say what the problem is. I would suggest though to use `Value` here, and not `Value2` – Tim Williams Apr 25 '18 at 21:04
  • @TimWilliams I think I have it figured out -- I wasn't extending my output range to accommodate the unmerged size. Regarding the use of value instead of value2, what's your reasoning for this? – Gregory Apr 25 '18 at 21:16
  • If your data has any dates in it then they'll all be converted to numeric format: `Value` won't mess with your data at all. – Tim Williams Apr 25 '18 at 21:25
  • @TimWilliams Very interesting. I was not aware of this limitation for .value2; it seemed to be the clear winner across the board...until you shared this with me, that is LOL – Gregory Apr 25 '18 at 21:28
  • https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – Tim Williams Apr 25 '18 at 21:31
  • @TimWilliams It's `Value2` that doesn't do any conversion. – IvenBach Apr 25 '18 at 22:06
  • @IvenBach - if I enter "3/16/2018" into a cell and select it, then enter `? Selection.Value2` in the Immediate pane I get `43175`. If I use `Value` I get `3/16/2018`, which is what I'd prefer to get if I were putting a whole range into an array. – Tim Williams Apr 25 '18 at 22:30

0 Answers0