if cell is blank move to next cell vba

  • I created a macro to round values in a spreadsheet to 3 sig digits, but I am struggling in getting the code to format values that are past an empty cell - it stops right at the empty cell, but I want this macro to be applied to all the values on a sheet and move to the next cell after an empty cell.

    I could only figure out how to apply it to rows and columns so I created two pieces to it (which isn't a big deal for me).

    I don't have a lot of experience here, but any help is much appreciated!

    Here is my code:


    Code
    1. Do Until ActiveCell.Value = ""
    2. ActiveCell.Select
    3. Format_Sig_Figs
    4. Round_Calc
    5. ActiveCell.Offset(1, 0).Select
    6. Loop
    7. End Sub


    Thanks in advance!


    Moderator Note: I have added code tags for you. Please read the forum rules you agreed to abide by.

  • I have large sets of data that are generated from a system and without a macro it is hard to ensure all of the data is edited to 3 significat digits without missing a few points. I hope this helps.

  • How would I best apply a code to allow the macro to modify my data without stopping in areas where there is a space?

    The code above has worked very well, except in instances where there is an empty cell, it does not continue to the next cell without selecting the areas again. This is rather inconvenient as I often have data sets separated by an empty cell.

    thanks in advance

  • what are these Procedures?


    Format_Sig_Figs

    Round_Calc


    do the cells contain Text or Formulas?


    Which column are you using?


    It might be easier an example of your workbook.

  • This code will make all cells that contain text in column A Bold.


    Code
    1. Dim rRng As Range
    2. Set rRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    3. On Error Resume Next
    4. Set rRng = rRng.SpecialCells(xlCellTypeConstants)
    5. On Error GoTo 0
    6. rRng.Font.Bold = True
  • Hi MD,


    Your code literally says to stop when you get to a blank cell, just redefine the end point somehow. Eg do until activecell.row = 1000 or wherever your last table row is likely to be.

    I literally copied this code from another sheet that had a similar code to what I needed to apply. if I redefine the activecell - would the code be changed to this?

    Do Until ActiveCell.Row = "1000"

    I'm literally a novice to this. I appreciate everyone's help!

  • These pretty much dictate the significant figure and rounding calculations to be used:


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.