Copy and Paste using VBA ignoring blanks and stacking data

[HHJCPETER]

I have a spreadsheet with a bunch of formulas in Cells B4:E100 in Sheet1. Many of the formulas will show up blank information in the event I do not have any data in Column A. I then have formulas which perform a similar function in Cells G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100. What I need to do is to take the data from the last 4 sets of cells (G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100) and stack then one on top of the other into Sheet2 starting in Cell A2. I need the code however to be smart enough to realize that if there are blanks which exist in any of those sets of cells to skip over to the next set of data I which to copy and start pasting that set of information without leaving any blanks. So far example, if I only have data between Cells A4:A25, I want the VBA code to go only through cells G4:Q25 and then start pasting without any spaces S4:AC25 and so on. Could someone please provide me with the code necessary to make this work if its at all possible? Much appreciated. Thanks.

 

[Rick Rothstein]

Two questions for clarification...

1. Do you want to retain the columns from each set? Or did you want all data to end up in Column A when finished?

2. This part of one of your sentences was not clear "if there are blanks which exist in any of those sets of cells to skip over to the next set of data"... does this mean if there is as little as a single blank cell in the set, don't copy anything from that set?

 

[HHJCPETER]

To answer your first question, yes I do want to retain the columns from each set. The answer to question number two is No. I want the data to copy until there is a blank and then skip over to the next group of cells. I do not want there to be any blanks in cell Sheet2 until the last set of data has been copied. Any help on this would be greatly appreciated.

 

[HHJCPETER]

Does this help to clarify a little what I am trying to accomplish?

 

[Rick Rothstein]

Does this help to clarify a little what I am trying to accomplish?

Not completely. You have four sets of 97 rows by 11 columns to work with. In each of them is data. What I am having trouble visualizing is where the blanks can be. One possibility is only at the end. In other words, you may have, say, 5 rows of completely filled in data (no internal blank cells) with any blanks located under them. If that is what you have, the stacking idea is easy. On the other hand, you could have blank cells scattered about within your data. If that is what you have, then this statement of yours is not clear to me "..if there are blanks which exist in any of those sets of cells to skip over to the next set of data..". Would that mean take all cells within the set above (above and to the left?) the blank and ignore any other data in the set and move on to the next set and do the same. Can you clarify this for me?

 

[HHJCPETER]

My apologies, you are right when I read it back it wasn't clear to me either the point I was trying to make. So my four sets of 97 rows by 11 columns all have the same formula in it referencing data in Column A. The formulas all read in this format, IF(CellA = "", "", Output Some specific information). So unless something exists in column A, there will be blanks which show up for some of the rows in the four sets of 97 rows by 11 columns. What I want to do is to stack only the data which comes through from the IF statements I am using and to ignore any areas which blanks exist. The answer to your questions is therefore yes, I would want to take all cells within the set above and to the right the blank and ignore and other data in the set and move on to the next set and do the same. I am new to this message board so I am not sure if this is possible, but I would be more than happy to post the file if that makes it easier to see what I am trying to accomplish.

 

[HHJCPETER]

So here is what the code looks like for what I am trying to do for where I currently have data in Column A. The part that I'm struggling with is that I only want to copy the areas where data actually exists due to the fact that I have many blanks from my IF Statement. Not sure if this helps to clarify. Here is the code.

Sub Macro1()
'
' Macro1 Macro
'


'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("S4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("A28").Select
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-117
Range("AE4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-93
Range("AQ4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=21
Range("A41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A54").Select
End Sub

 

[Rick Rothstein]

Does this macro do what you want...

Sub StackRanges()
  With Worksheets("Sheet2").Range("A2")
    .Resize(96, 11).Value = Worksheets("Sheet1").Range("G4:Q100").Value
    .Offset(96).Resize(96, 11).Value = Worksheets("Sheet1").Range("S4:AC100").Value
    .Offset(192).Resize(96, 11).Value = Worksheets("Sheet1").Range("AE4:AO100").Value
    .Offset(288).Resize(96, 11).Value = Worksheets("Sheet1").Range("AQ4:BA100").Value
    .Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1).SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub

 

[HHJCPETER]

Yes. I just need something now to delete the blank rows between the stacked data in Sheet2 and its exactly what I am looking for. Thanks.

 

[Rick Rothstein]

Yes. I just need something now to delete the blank rows between the stacked data in Sheet2 and its exactly what I am looking for. Thanks.

Are you saying the code I posted is not deleting the blank rows? Just so you know, I tested the code before posting it and it did delete them on my XL2003 test workbook.