Hi Folks,
The purpose of this code is to copy all values generated from a formula in column 'B' and paste those values in column 'D'. Next the code copies all the values generated from a formula in column 'C' to the next available row in column 'D'. In the end, I should see all values for column 'B' and 'C' in column 'D' with no blank rows.
The problem is: the code seems to create tones of blank rows in column D between the values generated from B and C.
Example of desired output below:
Code below (except of relevant pieces; no Dims)
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2, Criteria1:="<>"
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=1
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
ActiveWindow.SmallScroll ToRight:=-2
Call Application.Calculate
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2
LastRow = ActiveWorkbook.Worksheets("Rename_Folders").Cells(Rows.Count, 4).End(xlUp).Row
NextRowExport = LastRow + 1
ActiveWorkbook.Worksheets("Rename_Folders").Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWorkbook.Worksheets("Rename_Folders").Range("D" & NextRowExport).PasteSpecial xlPasteValues
Call Application.Calculate
Columns("B:C").EntireColumn.Hidden = True
Range("D2").Activate
The purpose of this code is to copy all values generated from a formula in column 'B' and paste those values in column 'D'. Next the code copies all the values generated from a formula in column 'C' to the next available row in column 'D'. In the end, I should see all values for column 'B' and 'C' in column 'D' with no blank rows.
The problem is: the code seems to create tones of blank rows in column D between the values generated from B and C.
Example of desired output below:
Column B | Column C | Column D |
TextB1 | TextC1 | TextB1 |
TextB2 | TextC2 | TextB2 |
TextB3 | TextB3 | |
TextB4 | TextB4 | |
TextC1 | ||
TextC2 |
Code below (except of relevant pieces; no Dims)
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2, Criteria1:="<>"
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=1
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
ActiveWindow.SmallScroll ToRight:=-2
Call Application.Calculate
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2
LastRow = ActiveWorkbook.Worksheets("Rename_Folders").Cells(Rows.Count, 4).End(xlUp).Row
NextRowExport = LastRow + 1
ActiveWorkbook.Worksheets("Rename_Folders").Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWorkbook.Worksheets("Rename_Folders").Range("D" & NextRowExport).PasteSpecial xlPasteValues
Call Application.Calculate
Columns("B:C").EntireColumn.Hidden = True
Range("D2").Activate