if cell is blank move to next cell vba

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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
    Do Until ActiveCell.Value = ""
ActiveCell.Select
Format_Sig_Figs
Round_Calc
ActiveCell.Offset(1, 0).Select
Loop
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
    Dim rRng As Range

Set rRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
Set rRng = rRng.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
rRng.Font.Bold = True
    Quote from Justin Doward

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login