0

I have a sheet where there is data from A3-A6. But this can be dynamic. The number of rows can change. I have formulas written in D3 all the way till BU3. I would like to extend these - Autofill to D6 automatically. But if the data is till A7, the formula should extend to A7 as well. The code which i have written is below. The problem is without there being data in A7 the formula is still getting copied over there. Also there is the next set of data from A19 which should not be impacted by this formulas. A6-A19 are blanks

Sheets("Working").Select
Set sht = ActiveSheet
Dim LR As Long
                                         
LR = sht.Range("A3").CurrentRegion.Rows.Count
Range("D3:BU3").Select
Selection.AutoFill Destination:=Range("D3:BU" & LR), Type:=xlFillDefault
braX
  • 11,506
  • 5
  • 20
  • 33
Swetha Khatri
  • 21
  • 3
  • Sheets("Working").Select Set sht = ActiveSheet Dim LR As Long LR = sht.Range("A3").CurrentRegion.Rows.Count Range("D3:BU3").Select Selection.AutoFill Destination:=Range("D3:BU" & LR), Type:=xlFillDefault – Swetha Khatri Jul 23 '20 at 09:30
  • 1
    You could probably benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) – John Coleman Jul 23 '20 at 09:33
  • Are A1 and A2 blank or do they have data? – Super Symmetry Jul 23 '20 at 09:50
  • A1 and A2 are headers – Swetha Khatri Jul 23 '20 at 09:51
  • So, they will be part of the `CurrentRegion` and will therefore be counted in `.Rows.Count`. Are you expecting this? – Super Symmetry Jul 23 '20 at 09:52
  • No. I do not want these 2 rows to be included. The formulas needs to be copied only from D3 till the last row. And the range for last row should start only from a3 till last row with data until a19. Can this be done? Pls advise – Swetha Khatri Jul 23 '20 at 09:55
  • Are you familiar with [the `Range.End(..)` Property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.end)? – Chronocidal Jul 23 '20 at 09:58
  • 1
    As @Chronocidal suggested, have you tried `LR = sht.Range("A3").End(xlDown).Row`? You must make sure cells below A6 are empty. Sometimes they have invisible characters (e.g. spaces or `""` resulting from a formula). Select the cells below a6 and press DELETE to clear contents and try again. I tested you code as it is and it actually works. – Super Symmetry Jul 23 '20 at 10:04
  • 1
    You also need to cap `LR` after setting its value (e.g. `If LR > 19 Then LR = 19`). This is because if you add data until A19, `End()` and `CurrentRegion` will extend to the end of the next set of data. – Super Symmetry Jul 23 '20 at 10:08
  • Ok let me re try. Thank you – Swetha Khatri Jul 23 '20 at 10:18
  • Super symmetry - this worked perfectly fine. Will this code also work to define the last row when only one row exists. But I need to keep in case in future we have greater than one row to identify last row? – Swetha Khatri Jul 23 '20 at 16:25

0 Answers0