Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how to find first blank cel in a row

0

Every day I read the price values of my funds. To get an overview of the evolution I make a graph. In this graph the oldest date should be on the left and the youngest on the right.

Now I do the following with a macro, so the youngest date is on the left and the oldest on the right.

column a1 : name fund

column b1: current date

column a2: name 1st fund

column b2: current value

Now I copy - with a macro - column B and select column C and choose Insert. This will move all existing columns 1 row to the right. But because of this the last data will be on the left instead of the right.

Answer
Discuss

Answers

1
Selected Answer

I looked at your macro and took out all the Select/Selection instructions. They aren't needed. I didn't add a sheet reference which is necessary for this kind of code. The procedure will run on the ActiveSheet and insert a column there whether you wanted to or not. Sooner or later this will cause you data loss. Until you fix this make double-triple sure which sheet is active when you run the code. Best practice: make it a rule to always run the code from the worksheet, never from the VB Editor window.

Sub groei_Reviewed()
    ' Variatus @TeachExcel 06 May 2020
    ' groei Macro

    Range("H2:H18").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G2:G16").Copy Destination:=Cells(2, "H")
    Application.CutCopyMode = False
    Cells(18, "H").FormulaR1C1 = "=RC[-4]"
    Cells(2, "G").Select                     ' there still is a problem here
End Sub

If the data are left-to-right so will the chart. There is no cure for that. Therefore you should consider setting up another table - whether at the bottom of the existing or on another tab - just for use of the chart where you reverse the order of values. Depending upon what you need for your chart and your prowess at programming you might either add a small loop to your existing code that updates the table with changed values, or create a static reference. For this purpose INDIRECT/ADDRESS might come in handy.

=INDIRECT(ADDRESS(3,7,1,1,"samenvatting"))

The formula will fetch the value of G3 (Cells(3, 7)) regardless of which data currently occupy that place, meaning the reference will not change as you insert columns. You can skip the tab's name if the formula is on the same tab.

If you want to invest a little more time in formula design, instead of typing, you can replace row and column references with counters created from, well, row and column references.

=ROW() will return the number of the row in which the formula resides. If it's in row 60 it will return 60. =ROW()-57 will return 3 and you can replace the 3 in the ADDRESS function with this function. Then, as you copy the formula down it will count from 3 up in rows 60 and up.

The same works analogue for =COLUMN(). But you need to count down instead of up. = 31-COLUMN() will produce 30 if placed in column A (= Column 1) and counts down from there as you copy to the right. If you need the 30 in column G the formula would be =37 - COLUMN().

The formula below would return the value from Z3 to G60 and you could copy it both to the left (up to column Z) and down.

[G60] =INDIRECT(ADDRESS(ROW()-57,33-COLUMN(),1,1,"samenvatting"))
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login