PDA

View Full Version : VBA to Add ListItems to a ListView



simora
04-28-2013, 01:46 AM
I'm trying to add ListItems from a Dynamic range to a Listview.
This is the code that I have.

Private Sub UserForm_Initialize()

' To fill ListView1 with content from a worksheet range

Sheets("Sheet1").Activate

With ListView1
.View = lvwReport
With .ColumnHeaders
.Clear
.Add , , "Account #", 100
.Add , , " Name", 150
.Add , , " Total", 80
End With
.ListItems.Add , , Sheets(1).Range(Range("A2"), Range("C65536").End(xlUp))

.HideColumnHeaders = False
.Appearance = cc3D
.FullRowSelect = True

End With
End Sub


It works when I add just 1 item in Col A, but I want to add all the data in the range from Col A to the last used row in column C .

Cant seem to find good info on Listview. Any help appreciated.

Thanks

snb
04-28-2013, 03:49 AM
for each cl in sheets(1).columns(1).specialcells(2)
.listitems.add,,cl
next

simora
04-28-2013, 03:16 PM
snb: Thanks.

However, that only populates column 1.

I did get t his to work like this.

With Sheets("Sheet1")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
i = 1
With ListView1
For i = 1 To LR
Set cell = Cells(i, 1)
.ListItems.Add , , cell
.ListItems.Item(i).ListSubItems.Add , , cell.Offset(0, 1).Text
.ListItems.Item(i).ListSubItems.Add , , cell.Offset(0, 2).Text
Next i
End With

Thanks for your input.
BTW: How do you modify sheets(1).columns(1).specialcells(2) to encompass multiple columns ? & Can you choose a Non-Contiguous Range ? If so, HOW ?

snb
04-29-2013, 01:22 AM
Sub M_snb()
sn=Sheets("Sheet1").usedrange.resize(,3)

With ListView1
For j=1 to ubound(sn)
if sn(j,1)<>"" then .ListItems.Add , , sn(j,1)
for jj=2 to ubound(sn,2)
.ListItems(j).ListSubItems.Add , , sn(j,jj)
next
Next
End With
end sub

simora
04-29-2013, 07:04 AM
snb:

Haven't tested it yet, but will later.

Thanks

simora
04-29-2013, 11:34 AM
snb :

Got a chance to test it. It didn't work.
I get a compile error at this point.
For j = 1 To UBound(sn)
Compile Error; Expected Array

I Have NOT used UBound extensively. Not sure how to fix it.

Thanks

snb
04-30-2013, 03:18 AM
I think 'sheet1' is empty.

simora
04-30-2013, 05:56 PM
snb:

I was able to step through the code. This section:

For jj=2 To UBound(sn,2) and other array functions don't seem to like when they are blank cells or irregular formats in the data.
I have a blank cell in the data range.
I was trying to understand what the code was doing.( A learning experience ). Exactly where in your code can I tell it to start on Row 2 of my data rather than row 1, since I'm already giving it the headers that I want to use.

Thanks for your info & assistance.