Excel macro - insert only non-empty cells from one sheet to another

Question

Below is the code that I use to copy cells from one sheet and paste into another.

Sheets("codes").Select Range("A5:A100").Select Selection.Copy Sheets("Sheet2").Select Range("B28").Select ActiveSheet.Paste 

The problem with this is with some cells in this range, but I don't want them to be copied to Sheet2. I have an idea from here , but this method is too long. Is there a way by which I can iterate when selecting, and check if the value is nonempty and insert. This way I can insert another text (like #NA) into empty cells.

Answer 1

It looks like you can make some common rookie mistakes here (all is well, we all did it).

---

VBA Example with Explanatory Explanations

TIP: Do not try to use Select or Copy. Why use select when all you have to do is reference the cells themselves? For example, instead of using

 Sheets("codes").Select Range("A5:A100").Select Selection.Copy Sheets("Sheet2").Select Range("B28").Select ActiveSheet.Paste 

Just use

 dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables set myBook = Excel.ActiveWorkbook set mySheet = myBook.Sheets("codes") set myOtherSheet = myBook.Sheets("Sheet2") dim i as integer, j as integer 'Define a couple integer variables for counting j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28) for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . . if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell value is blank. If it isn't then it will . . . myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable. j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2. end if next i 'This triggers the end of the loop and moves on to the next value of "i". 

I did the same thing all the time when I was just starting, and it never works correctly. "Select" causes errors on the left and right. Use my code, read the comments, and everything will be all right. Quick WARNING: I do not have Excel on this computer, so I could not verify the code. If for some reason it doesn’t work, leave me a comment, and tomorrow I will fix it at work.

The above code will completely omit empty cells when copying data to a second sheet. If you want to enter specific text for empty cells (for example, "N / A"), you can use the following:

  dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables set myBook = Excel.ActiveWorkbook set mySheet = myBook.Sheets("codes") set myOtherSheet = myBook.Sheets("Sheet2") dim i as integer, j as integer 'Define a couple integer variables for counting j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28) for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . . if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell value is blank. If it isn't then it will . . . myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable. else 'If the cell is blank, then . . . myOtherSheet.Cells(j,2).value = "N/A" ' . . . place the text "N/A" into the cell in row "j" in Sheet2. end if 'NOTICE we moved the "end if" statement up a line, so that it closes the "if" statement before the "j = j + 1" statement. _ This is because now we want to add one to the "j" variable (ie, move to the next available row in Sheet2) regardless of whether the cell in the "codes" sheet is blank or not. j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2. next i 'This triggers the end of the loop and moves on to the next value of "i". 

Answer 2

Simply:

  Sheet1.Range("A1:a500").SpecialCells(xlCellTypeConstants).Copy Sheet2.Range("b2") 

I used xlCellTypeConstants , but there are many other possibilities.

Sheet1 usually equivalent to Sheets("Sheet1") . The first is the name in VBE (the programmer’s view), the second is the name in the user interface (user view). I usually prefer the fiirst syntax because it is shorter and allows you to rename sheets (for the user) without affecting the code.

Answer 3

If you do not need formatting, I would use the following. All he does is copy the range that you specified on the sheet into a variable, go through this variable, check that the cells are empty and placed on any line that you like. It is nice and fast. If you want to keep formatting, you can only insert special formats into the output range.

 Sub CopyNonBlankCells(rFromRange As Range, rToCell As Range, sSubIn As String) 'You have three inputs. A range to copy from (rFromRange), a range to copy to (rToCell) and a string to put in the blank cells. Dim vData As Variant, ii As Integer, jj As Integer 'Set to a variable since it quicker vData = rFromRange.Value 'Loop through to find the blank cells For ii = LBound(vData, 1) To UBound(vData, 1) 'Loop the rows For jj = LBound(vData, 2) To UBound(vData, 2) 'Loop the columns 'Check for empty cell. Quicker to use Len function then check for empty string If VBA.Len(vData(ii, jj)) = 0 Then vData(ii, jj) = sSubIn Next jj Next ii 'Output to target cell. Use the 'With' statement because it makes the code easier to read and is more efficient With rToCell.Parent .Range(.Cells(rToCell.Row, rToCell.Column), .Cells(rToCell.Row + UBound(vData, 1) - 1, rToCell.Column + UBound(vData, 2) - 1)).Value = vData End With End Sub 

And name it with:

 Call CopyNonBlankCells(Sheets("codes").Range("A5:A100"), Sheets("Sheet2").Range("B28"), "Non-blank")