How to add an item to the end of a dynamic range and sort alphabetic

[mdavid]

Hi, I want to add an item to the 1st empty row of a dynamic range, and then sort the range alphabetically.
The code I have to add the item - which does not produce an error, but also doesn't add the item to the range is:

Private Sub AddActionBtn_Click()
Dim rng As Range
Dim N As Long
Dim ws As Worksheet
ActiveSheet.Unprotect
Set ws = Worksheets("Actions")
   N = Cells(Rows.Count, "A").End(xlUp).Row + 1
   Cells(N, "A").Value = TextBox2.Value
End Sub

I enter the new item into TextBox2 and then click CommandButton AddActionBtn.

Once I've added the the new item I would like to sort the range on column A alphabetically.

Thanks for any help
David

 

[Marc L]

Hi,

to sort the dynamic range of first column just start
with ActiveSheet.UsedRange.Columns(1).Sort …
- or use Worksheets if the sheet is not active -
and add its parameters as you can read in VBA inner help
or just using the Macro Recorder …
​

 

[mdavid]

Hi Marc, thanks for your reply, before I sort I first need to add the new item, any idea how I do that - and why the above code doesn't work?

Thanks
David

 

[Marc L]

Logic error as always !
It seems you forgot the worksheet reference of Cells …

Easy way : activate the Macro Recorder, operate manually :
you will get your own free base of code !
Then compare with your previous code so you'll easily know why …
​

 

[mdavid]

Hi Marc, Thanks for your help - this is the code that worked for me:

Private Sub AddActionBtn_Click()
Dim rng As Range
Dim N As Long
Dim ws As Worksheet
ActiveSheet.Unprotect
Set ws = Worksheets("Actions")
With ws
   N = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   .Cells(N, "A").Value = TextBox2.Value
   .Cells(N, "C").Value = "1"
   .Range("A3:C" & N).Sort key1:=.Range("A3:A" & N), _
   order1:=xlAscending, Header:=xlNo
End With
End Sub

Now my problem is the range is a dynamic range refered to by:
=OFFSET(Actions!$A$2,0,0,COUNTA(Actions!$A:$A)-1,3)
and the initial number of rows doesn't increase when I add a new row to the range. How can I get the number of rows to increase when I add a row.

Thanks for your help
David

 

[Marc L]

If first row used is #1 so you can use the worksheet property UsedRange

like UsedRange.Rows.Count ...​

 

[mdavid]

Hi Marc,
I tried this:

With ws
   N = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   .Cells(N, "A").Value = TextBox2.Value
   .Cells(N, "C").Value = "1"
   .Range("A2").Resize (N)
   .Range("A3:C" & N).Sort key1:=.Range("A3:A" & N), _
   order1:=xlAscending, Header:=xlNo
End With

And get "Invalid use of property" on the .Resize

What am I doing wrong?

Thanks
David

 

[mdavid]

After a little more Googling around, this worked:

nmdRNG = "ActionsLstUpdatable"
With Range(nmdRNG)
    .Resize(.Rows.Count + 1).Name = nmdRNG
End With

Thanks for your time
David