Jan 31 2021 05:58 AM
Jan 31 2021 05:58 AM
New to Macros. Managed to record a macro. When I run the macro and then delete rows, certain cells don't update their values. For instance C8 should equal C41. I deleted row C39-60 but obviously the row C41 is not deleted, row C61 just moves up to become C41. C8 doesn't understand that. I have no idea how to write code in VBA.
Jan 31 2021 10:56 AM
I rarely write macros or VBA code. The reason: although there are definitely legitimate uses for macros/VBA, I've many a time seen it happen that people write code thinking that's the only way to accomplish a given task, without realizing that Excel's built-in functions offer a lot of power and can often accomplish more efficiently and reliably whatever the task is.
So, my goal in saying all that isn't to say "Don't ever learn to write code," but rather to set the stage for asking "What are you actually trying to do?" What is the task here? What is the situation to which you're bringing the toolkit that is Excel? What kind of information is in those rows, the ones you're keeping and the ones you delete? Why are you deleting rows? And so forth.
The purpose of all those questions : you can help the folks here in the Microsoft's techcommunity help you by describing not just your frustration with the macro you've recorded and run, but the bigger picture. In fact, if you could post a copy of the Excel workbook that has frustrated you along with that description of the bigger picture, it would be even more likely we could help.
Jan 31 2021 03:27 PM
That is a general problem with using direct cell references from VBA. If changes are made to the worksheet it is normal for the code to break. The way to make the code more robust is to use defined names or Excel Tables (I restrict myself to defined names or table references on the worksheet too, but that is not general practice).
If you define a named range you will be able to go to it to see what it contains and if you delete rows the range adjusts with the content. Within the code you have statements such as
Sub test() Dim Rng As Range Dim v As Variant Dim f As String Dim n As Double Set Rng = [myrange] 'This range object contains every property of the worksheet range v = Rng.Value 'This variable will be an array contining the values from the range n = v(4, 2) 'This number is an element of the array f = Rng.Item(1).Formula 'The string f will contain the formula held in the first cell of the range End Sub
which allow you to read the properties of range such as size and colours, return the values it contains or the formulas used to generate the values. As the named range moves the code follows.