VBA - inseritng a table

[Barrakooda]

Hi all

I am using the following VBA to select data on a sheet, does anyone know what to put after .Select to insert a table?

I have tried recording a macro, but seems to limit itself to the range selected while recording. The range will vary.

Sub PickedActualUsedRange()
Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

Thanks in advance

 

[Michael M]

Are you talking about inerting a Pivot table ?
You don't need to Select any data, simply make sure your activecell is withing the data range, the recorder will select ALL data

 

[Barrakooda]

Thanks Michael

No it's not for a pivot table, just a simple table. When I recorded macro previously it put in Table 4 I think from memory. I simply clicked on Table in Insert menu.

I know this can be done manually butt this will be part of a grander macro to automate quite a few actions.

 

[Michael M]

Ok, this will select the Usedrange of a worksheet.....but try to avoid using Select whenever possible.....it's generally not required !!
Once you've selected the entire range what do you want to do with it ???

Sub MM1()
ActiveSheet.UsedRange.Select
End Sub

 

[Michael M]

Darn, I timed out....this will add the table

Sub MM1()
    With ActiveSheet
       .UsedRange
       .ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table2"
    End With
End Sub

 

[Barrakooda]

I want to insert a basic table with headers, different coloured rows etc over the data selected. Since when I dump all my data on to different sheets they will all have varying ranges. More the rows than anything else.

What's the issue with using .Select in VBA?

 

[Michael M]

See my previous post......I didn't use Select
You say you are creating a grander macro !
If you use Select.Selection throughout you will find the process could be slowed considerably, especially if there are many sheet changes, range changes, etc.
By avoiding this, your code will be quicker, cleaner, and easier to debug.

 

[Barrakooda]

ok Thanks, sorry missed your previous post due to time out, will give it a shot.

 

[Barrakooda]

Perfect, thanks again

 

[Michael M]

Glad it helped....