Results for "discover regular expressions new functions"

Hi, I totally agree with Saboh. But if you still want to use VBA, here is some starting points :

Last row based on column A :

LR = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).Row

Writing formula with VBA :

Sheets("SheetName").Range("A1").Formula = "=SUM(B1:B" & LR & ")"

You can also use generic formulas like =SUM(B:B), but those ones use quite more ressources.

Hello Noura and welcome to the forum,

If I may, you're not following the right approach. There are native Excel tools that allow you to do this in a simple and intuitive way, without using VBA (old, security concerns, subject to bugs…).

Based on your description, it sounds like you're looking for tables. Here's a great tutorial that gives a comprehensive overview of their features: Excel table: comprehensive tutorial with examples.

For your information, in addition to automatically extending your formulas, this will also apply to pivot tables.

Hi everyone,

I'm an investment analyst and new to using VBA. I have a spreadsheet where I add new data every month (one new row each time). I want to use VBA to:

• Find the last row with data
• Automatically extend formulas to include the new row
• Update named ranges or charts so they stay current

What is the best way to do this in VBA?
Any simple examples or advice would be really helpful.

Thank you!

Best regards,

Noura

I think I get what you're aiming for with the macro. So, when you open the file and select a cell like A7, you want the macro to grab certain cells from that row (like A7, C7, E7, G7) and paste them into a new workbook, right?

Hello and welcome to the forum

Try with the code herein

Sub Macro1()
Dim newwb As Workbook
Dim r As Integer
Dim TS As ListObject

Set TS = ThisWorkbook.ActiveSheet.ListObjects(1)

On Error Resume Next
r = TS.ListRows(ActiveCell).Range.Row - TS.HeaderRowRange.Row
If r = 0 Then MsgBox "No cell selected in table !", vbCritical, "Wrong selection": Exit Sub 'if selected cell in not in table range
On Error GoTo 0

Set newwb = Workbooks.Add
With ActiveWorkbook.ActiveSheet
    .Range("A1") = TS.DataBodyRange(r, 1).Value
    .Range("A2") = TS.DataBodyRange(r, 3).Value
    .Range("A3") = TS.DataBodyRange(r, 5).Value
    .Range("A4") = TS.DataBodyRange(r, 7).Value
End With
End Sub

rem : Please think to update the reference of your excel version in your account as "English" does not help. Use excel 2008, 2016,;... or if you are a MacUser --> MAC2011, MAC20xx

Sincerely

I would like the Macro to do the following:

When I open the file and select, for example, A7

Then I run the macro

In the macro, I have a setting that if I first selected a cell from row 7, then the macro places the 1st, 3rd, 5th and 7th cells from row 7 in a new workbook in the following way

1. in A1

3. in A2

5. in A3

7. in A4

Also, I would like to choose the save location