VBA to copy Data from one column to another wihtout blank cells

[robertmwaring2]

I have been struggling for days to try and figure what I am doing wrong, but for the life of me I cannot get it. I am TOTALLY new to VBA so it makes it all that much more difficult. Here is my scenario. I have four ranges of information (AEO1:AFF36,AFH1:AFY36,AGA1:AGR36,AGT1:AHK36). Not all rows in each column contain values, if a column has values at all, BUT each cell within the ranges does have a formuls. My goal was to stack each column without blanks (cells that show no value). I had hoped to find a way via VBA to do so, but as I am new I am only able to get the columns stacked via formulas and now have one column (AHR1:AHR648) that contains all the information from one range (AEO1:AFF36) but there are blank cells intermitently throughout the column. I have tried repeatedly (using VBA) to copy that column to another (AHT1) to eliminate the blanks (cells that SHOW no value) and ONLY have values (no formulas copied) but no matter what I try, I cannot get rid of the blank cells. Below is the code I am using:

Sheets("Info Sheet").Range("AHW1:AHW648").Copy
Sheets("Info Sheet").Range("AHZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

I have tried multiple other forms and variations of this but NONE seem to work. Some even copy the formulas over, which I do not want. I could write another formuls to do this, but it would be ridiculously long (at least with any way I know how) and I would have to do it four times (have four ranges to compile). I was hoping to find a way to just creat the one column directly from the original range, but I hate to appear greedy (lol). At this point, if someone could help me correct what I've posted, that would be great - but if you could actually help me accomplish my original goal, that would be INCREDIBLE.

Thanks in advance for the help.


EDIT:
Just realized that in the code I posted, it references Column AHW - I copied the information manually from the AHR column to the AHW column to get rid of the formulas in the cells to see if by chance that was the issue with it not eliminating the blank cells, but alas, that was no fix either.

 

[Fluff]

How about

Sub Robertmwaring()
   With Sheets("Info Sheet").Range("AHR1:AHR648")
      .Value = .Value
      .SpecialCells(xlBlanks).Delete xlUp
   End With
End Sub

 

[robertmwaring2]

@Fluff,

Thanks again for the assistance. I was up til about 2:30 this morning trying to find a work around and came up this this that seems to work perfectly, albeit probably not the most efficient way to accomplish it:

'copies recipe uom list from info sheet to production sheet, values only, no blanks
Sheets("Info Sheet").Range("AHZ1:AHZ648").Select
Selection.AutoFilter
Sheets("Info Sheet").Range("$AHZ$1:$AHZ$648").AutoFilter Field:=1, Criteria1:="<>"
Sheets("Info Sheet").Range("AHZ1:AHZ648").Select
Selection.Copy
Sheets("Production Sheet").Range("G8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Info Sheet").Range("$AHZ$1:$AHZ$648").AutoFilter Field:=1
Application.CutCopyMode = False
Selection.AutoFilter


'copies recipe uom list from info sheet to production sheet, values only, no blanks
Sheets("Info Sheet").Range("AHX1:AHX648").Select
Selection.AutoFilter
Sheets("Info Sheet").Range("$AHX$1:$AHX$648").AutoFilter Field:=1, Criteria1:="<>"
Sheets("Info Sheet").Range("AHX1:AHX648").Select
Selection.Copy
Sheets("Production Sheet").Range("F8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Info Sheet").Range("$AHX$1:$AHX$648").AutoFilter Field:=1
Application.CutCopyMode = False
Selection.AutoFilter


'copies recipe quantities list from info sheet to production sheet, values only, no blanks
Sheets("Info Sheet").Range("AHV1:AHV648").Select
Selection.AutoFilter
Sheets("Info Sheet").Range("$AHV$1:$AHV$648").AutoFilter Field:=1, Criteria1:="<>"
Sheets("Info Sheet").Range("AHV1:AHV648").Select
Selection.Copy
Sheets("Production Sheet").Range("E8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Info Sheet").Range("$AHV$1:$AHV$648").AutoFilter Field:=1
Application.CutCopyMode = False
Selection.AutoFilter


'copies recipe list from info sheet to production sheet, values only, no blanks
Sheets("Info Sheet").Range("AHR1:AHR648").Select
Selection.AutoFilter
Sheets("Info Sheet").Range("$AHR$1:$AHR$648").AutoFilter Field:=1, Criteria1:="<>"
Sheets("Info Sheet").Range("AHR1:AHR648").Select
Selection.Copy
Sheets("Production Sheet").Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Info Sheet").Range("$AHR$1:$AHR$648").AutoFilter Field:=1
Application.CutCopyMode = False
Selection.AutoFilter

I'll give your code a try since it appears to be more streamlined and likely less taxing.