Copy column data consisting of blank cells

Question

I am into a situation where I need to copy a range from a excel sheet and paste it to another. I have done the following coding which is going well...

Dim mpn As String

mpn = Application.InputBox(prompt:="Input the MPN column name:")

mpn1 = mpn

mpn2 = mpn1 & ":" & mpn

Set currentSheet = wbSource.Worksheets(1)

lastRow1 = currentSheet.Range(mpn1).End(xlDown).Row

ThisWorkbook.Sheets("Sheet2").Range("F2:F" & lastRow1) = currentSheet.Range(mpn2 & lastRow1).Value

This coding goes perfectly well untill there is any blank cell in the column. Can anyone please help me on this particular situation.

`This coding goes perfectly well untill there is any blank cell in the column` And hence you should never use `xldown` to get the last cell with data :) Use [this](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Nov 07 '14 at 07:22
Also instead of prompting for the column name, use `.Find` to locate the column name. What if user types `Blah Blah` in the input box? See[this](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) on how to use `.Find` — Siddharth Rout, Nov 07 '14 at 07:30
@SiddharthRout thanks for the so prompt reply boss...but I got an error in this line lastRow1 = currentSheet.Range(mpn1 & .Rows.Count).End(xlUp).Row — user3305327, Nov 07 '14 at 07:31
Try this `lastRow1 = currentSheet.Range(mpn1 & currentSheet.Rows.Count).End(xlUp).Row` I am also assuming that `mpn1` is a valid column name. — Siddharth Rout, Nov 07 '14 at 07:35

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

To copy an entire column, reference your range with the .Columns() function.

You could use something like:

ThisWorkbook.Sheets("Sheet2").Columns("F") = 
    currentSheet.Columns(mpn1).Value

Another alternative would be to use the .Copy sub and specify a Destination for the copy:

currentSheet.Columns(mpn1).Copy 
    Destination:=ThisWorkbook.Sheets("Sheet2").Columns("F")
Application.CutCopyMode = false

This answer assumes both workbooks are saved with the same version of Excel. If one workbook is pre-2007, and one is 2007+, then the max number of rows allowed in a sheet will be different.

In that case, copying the entire column is not an option - check out Siddarth's answer for a longer solution to that extra complication. He checks for different number of rows to prevent the error.

edited May 23 '17 at 11:49

Community

1
1

answered Nov 07 '14 at 07:38

Richard Le Mesurier

29,432
22
140
255

This may give you an error if you are copying from xl2007+ to xl2003 :) – Siddharth Rout Nov 07 '14 at 07:43
I have shown what I was talking about in the answer that I have posted above :) – Siddharth Rout Nov 07 '14 at 07:56
+ 1 now it makes sense :) – Siddharth Rout Nov 07 '14 at 08:00

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Like I mentioned in the comments above, instead of prompting for the column name, use .Find to locate the column name. What if user types Blah Blah in the input box?

Also as mentioned in comments use xlUp rather than xlDown to find the last row to counter for blank cells and other issues you may face. See this

Is this what you are trying? (Untested)

I have commented the code so you should not having a problem understanding it. But if you do then simply post back :)

Sub Sample()
    Dim mpnCol As Long
    Dim ColName As String, strSearch As String
    Dim aCell As Range
    Dim wbSource As Workbook
    Dim wbInput As Worksheet, currentSheet As Worksheet

    '~~> Change this to the Mpn Header
    strSearch = "MPN"

    '~~> This you have declared in your code
    '~~> Change as applicable
    Set wbSource = "Someworkbook"

    Set currentSheet = wbSource.Worksheets(1)
    Set wbInput = ThisWorkbook.Sheets("Sheet2")

    With currentSheet
        '~~> Search for the mpn header in row 1. Change as applicable
        Set aCell = .Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            '~~> Column Number
            mpnCol = aCell.Column
            '~~> Converting column number to column name
            ColName = Split(.Cells(, mpnCol).Address, "$")(1)
            '~~> Getting last row
            lRow = .Range(ColName & .Rows.Count).End(xlUp).Row

            '~~> Checking for excel versions. Comment this if the copying
            '~~> will always happen in xl2007+ versions
            If lRow > 65536 Then
                MsgBox "Are you trying to copy from xl2007 to xl2003?. The number of rows exceed the row limit"
                Exit Sub
            End If

            wbInput.Range("F2:F" & lRow).Value = .Range(ColName & "2:" & ColName & lRow).Value
        Else
            MsgBox strSearch & " header not found"
        End If
    End With
End Sub

Nice comprehensive response - covers a lot more than the single issue OP has found so far. I can see him revisiting this as he learns more, and encounters further issues. — Richard Le Mesurier, Nov 07 '14 at 08:03
@RichardLeMesurier: `I can see him revisiting this...` Me too :) — Siddharth Rout, Nov 07 '14 at 08:04
@SiddharthRout and richard you guys have lots more knowledge on this thread and am trying those in my code too... — user3305327, Nov 07 '14 at 08:17
@SiddharthRout am getting this error "Method Range Failed in _worksheet failed" in this line currentSheet.Range(mpn1 & currentSheet.Rows.Count).End(xlUp).Row — user3305327, Nov 07 '14 at 08:21
What is `mpn1`? I never suggested that in the code above? :) — Siddharth Rout, Nov 07 '14 at 08:26

Copy column data consisting of blank cells

2 Answers2