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

Robertmwaring

New Member
Joined
Mar 8, 2019
Messages
15
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Robertmwaring()
   With Sheets("Info Sheet").Range("AHR1:AHR648")
      .Value = .Value
      .SpecialCells(xlBlanks).Delete xlUp
   End With
End Sub
 

Robertmwaring

New Member
Joined
Mar 8, 2019
Messages
15
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top