1819
01-28-2017, 08:41 PM
I have attached a sample worksheet which works to overwrite the values in column A if there is a value in the adjacent cell in column B.
So it copies and pastes all values in column B to the adjacent cell in column A. It ignores blanks.
The code I have used to select the cells which contain values in column B is:
Option Explicit
Sub Selector()
'5 - Overwrite cells in column C where the row has value from column M
'PURPOSE: Selects only cells with values or formulas and excludes blank values (ie ="" or +"")
'AUTHOR: Rick Rothstein (MrExcel MVP)
'SOURCE: www.TheSpreadsheetGuru.com
Dim rng As Range
Dim UnusedColumn As Range
Dim Target As Range
Dim Cancel As Boolean
With ActiveSheet
Set rng = Range("B1:B3000")
'Find a column with nothing in it
Set UnusedColumn = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).EntireColumn.Offset(0, 1)
'Create temporary calculation column to determine which cells to select (marked by an X)
Intersect(rng.EntireRow, UnusedColumn) = Evaluate("IF(" & rng.Address & "="""","""",""X"")")
'Make Selection
Intersect(UnusedColumn.SpecialCells(xlConstants).EntireRow, rng.EntireColumn).Select
'Remove Temporary Blank Calculations
UnusedColumn.Clear
End With
End Sub
and the code to overwrite column A is:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
'
Columns("A:B").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2, Criteria1:="<>"
Range("B1:B367").Select
Selection.Copy
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Unfortunately I need to use all this code in a much larger spreadsheet, where column A in the sample is at column C, and column B in the sample is at column M.
There are also 21,000 rows of data.
I simply cannot get the code in the sample file to work in the larger spreadsheet. What changes do I need to make to the code to make it run properly?
Many thanks.
18177
So it copies and pastes all values in column B to the adjacent cell in column A. It ignores blanks.
The code I have used to select the cells which contain values in column B is:
Option Explicit
Sub Selector()
'5 - Overwrite cells in column C where the row has value from column M
'PURPOSE: Selects only cells with values or formulas and excludes blank values (ie ="" or +"")
'AUTHOR: Rick Rothstein (MrExcel MVP)
'SOURCE: www.TheSpreadsheetGuru.com
Dim rng As Range
Dim UnusedColumn As Range
Dim Target As Range
Dim Cancel As Boolean
With ActiveSheet
Set rng = Range("B1:B3000")
'Find a column with nothing in it
Set UnusedColumn = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).EntireColumn.Offset(0, 1)
'Create temporary calculation column to determine which cells to select (marked by an X)
Intersect(rng.EntireRow, UnusedColumn) = Evaluate("IF(" & rng.Address & "="""","""",""X"")")
'Make Selection
Intersect(UnusedColumn.SpecialCells(xlConstants).EntireRow, rng.EntireColumn).Select
'Remove Temporary Blank Calculations
UnusedColumn.Clear
End With
End Sub
and the code to overwrite column A is:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
'
Columns("A:B").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2, Criteria1:="<>"
Range("B1:B367").Select
Selection.Copy
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Unfortunately I need to use all this code in a much larger spreadsheet, where column A in the sample is at column C, and column B in the sample is at column M.
There are also 21,000 rows of data.
I simply cannot get the code in the sample file to work in the larger spreadsheet. What changes do I need to make to the code to make it run properly?
Many thanks.
18177