VBA: if cell isn't empty, copy contents. if empty, move to next row.

jpringle1

New Member
Joined
Jan 4, 2019
Messages
7
OK so i just need a simple piece of VBA code which does the following things:

if cell B2 isn't empty, assign its value to a variable.
if it is empty, run this same code against the next row.
Continue until the bottom of the table is reached.

this piece of code of code is going to be adapted for a bigger project, so i will likely come back to ask for help on modifications a few times after this has been answered. The reason i only want a part of it answered at a time is because i want to understand the code so i can adapt it myself for future projects, and i'm not sure exactly which direction i want to take the code in yet.

Thank you
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
How many rows do you need to test ?
How many variables might there be ?
Does the 2nd variable replace the 1st variable ?
 
Upvote 0
How many rows do you need to test ?
How many variables might there be ?
Does the 2nd variable replace the 1st variable ?

For the sake of this example, let's say 20? But if possible I would like the amount to be dynamic, so if the size of the table changes, so does the amount of rows that get checked.

Also, there would only be one variable, so it would get replaced each time (I'm planning on inserting instructions for what to do with that variable in between row checks, but let's forget about that for now as I want to take this one step at a time)
 
Upvote 0
for the first bit you can try
Code:
If Not IsEmpty(Range("B2").Value) Then
Range("B2").Value = [your variable here]
Else
[code for when B2 is empty]
End If

depending on what your table looks like, like if it's on a sheet on its own or in a sheet with other data, looping through your table might be different
 
Upvote 0
Maybe this, x being your variable

Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
  For r = 2 To lr
     If Range("b" & r).Value <> "" Then x = Range("b" & r).Value
  Next r
End Sub
 
Upvote 0
Just a different method of looping (wouldn't have posted it except I had it written when Michael posted so might as well post it before I delete it).

Code:
Sub GetVAR()
    Dim myVar, myCell As Range
    For Each myCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If myCell.Value <> "" Then myVar = myCell.Value
    Next
End Sub

Although as you are doing something with the variable at each stage then you probably need

Rich (BB code):
Sub GetVAR()
    Dim myVar, myCell As Range
    For Each myCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If myCell.Value <> "" Then
            myVar = myCell.Value
            ' Do something with the variable here
        End If
    Next
End Sub
 
Upvote 0
Hello jpringle
Let me WELCOME you to the site. Here is a bit of code I've put together that I think will cover what you have told us so far.
Code:
[COLOR=black][FONT=Calibri]Option Explicit[/FONT][/COLOR]

  [COLOR=black][FONT=Calibri]Sub jPringle1()[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' https://www.mrexcel.com/forum/excel-questions/1086969-vba-if-cell-isnt-empty-copy-contents-if-empty-move-next-row.html[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' February 10, 2019 @ 5:20 pm[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' This is better than using a sheet name in case the user changes the tab name at a later date.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim bCell As Variant         ' Variable to hold value of cell in 'B' column[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim NumRows As Long    ' Number of data rows NOT counting heading[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim RwCntr As Long         ' Loop counter to count number of rows of data processed[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]RwCntr = 2[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]NumRows = ws1.Cells(Rows.Count, "B").End(xlUp).Row - 1  'Determine number of rows of actual data[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]With ws1[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    For RwCntr = 2 To NumRows + 1[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  If 'B' column cell is empty, get next cell, otherwise assign value to variable of bCell[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]        If .Cells(RwCntr, 2) <> "" Then[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]            bCell = .Cells(RwCntr, 2).Value[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]        End If[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]     Next RwCntr[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End With[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Application.ScreenUpdating = True[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End Sub[/FONT][/COLOR]

I hope this helps.

TotallyConfused
 
Last edited:
Upvote 0
Hi TotallyConfused, if I am reading what you have posted correctly and you are intending using the sheets codename rather than it's tab name shouldn't

Code:
  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR="#FF0000"]Sheets("Sheet1")[/COLOR]

be

Code:
  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR="#FF0000"]Sheet1[/COLOR]

although I don't think it makes much difference once it is assigned to a variable.
 
Last edited:
Upvote 0
Hi TotallyConfused, if I am reading what you have posted correctly and you are intending using the sheets codename rather than it's tab name shouldn't

Code:
  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR=#FF0000]Sheets("Sheet1")[/COLOR]

be

Code:
  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR=#FF0000]Sheet1[/COLOR]

although I don't think it makes much difference once it is assigned to a variable.

Hello Mark
You may be right, I don't know. The first time I was introduced to the idea of using the sheet code name rather than its tab name, was here on this site. I've written it this way a few times since then and never had any problems, though your suggestion would save a little typing. To be honest, I've always assumed that was the way to write it and have never thought anything more about it. When I put this routine together, I set up a dummy worksheet with several rows of data in column 'B', then ran my code. To make sure everything was being processed correctly, I then added a few more rows of data and ran this macro again. Looking at what others, including what you posted, I see mine is a little longer, but I tried to keep in mind that JPringle has more information to give us, so I tried to lay some of the basic groundwork now. For example, that is the reason I assigned the number of data rows to a variable, which can be accessed in any future code if needed, without having VBA make that calculation again. I don't know if this is good thinking or not. Most of you people are far more experienced than I am. I do want to THANK YOU for pointing out where I might have written it different because I'm always open to new and better ways of coding.

TotallyConfused
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top