0

I've been trying for hours to do a macro that selects a raw data dump. I'm doing it like this:

Range("A1").Select 'Start at A1
Range(Selection, Selection.End(xlDown)).Select 'Go all the way down
Range(Selection, Selection.End(xlToRight)).Select 'Go all the way to the rgt

It works for different size data dumps thanks to xlDown and xlToRight. I guess this is far from optimal, though.

Now, I need to format that selection as a table to generate a pivot table.

How can I store that selection into a variable or a Variant and then use it within other code to format is as table?

As if I had VAR0 and VAR1 and then:

ActiveSheet.ListObjects.Add(xlSrcRange, Range(VAR0:VAR1), , xlYes).Name 
= _"Table1"

I'm just learning VBA and using the macro recorder and am using some data to play with. I'd really appreciate some pointers! :)

I'm using Excel 2016.

Thanks!

10110
  • 2,353
  • 1
  • 21
  • 37

2 Answers2

1

You can set variables to the last row and the last column and then use those values to define your range. There are a variety of ways to do this, and I'm sure people will have pros and cons on the various methods, but if your data is pretty well laid out (with no blank rows, blank columns, or blank cells), I like:

last_row = Worksheets("<worksheet name>").UsedRange.Rows.Count
last_col = Worksheets("<worksheet name>").UsedRange.Columns.Count

Then your range for the table becomes Range(Cells(1,1), Cells(last_row,last_col)).

I should note you can also set a variable equal to a range:

Dim rng as Range
Set rng = Selection

That might work for you, though I should also note there are risks with working with Selections in code.

rryanp
  • 1,027
  • 8
  • 26
  • 45
  • Hi, thanks! I was thinking about making a named range. But this looks better. I declared the variables `As Long` and assigned them to `last_row = Worksheets("sheet1").UsedRange.Rows.Count` but I get `Run-time error '9': Subscript out of range.` I think im not referencing the sheet correctly... Something I forgot to mention is that the worksheet has other content to the right. Such as instructions and the button to run the macro. – 10110 Nov 06 '17 at 03:32
  • I'm guessing the out of range is an issue with the sheet name--is it definitely "sheet1" and not "sheet 1" or something slightly different? And having stuff out to the right could make it tough to get your column number. But if your original code worked to select the right range, maybe you could do something like: `Dim last_row As Long, Dim last_col as Long, Dim rng as Range, last_row = ActiveCell.End(xlDown).Row, last_col = ActiveCell.End(xlToRight).Column, Set rng = Range(ActiveCell, Cells(last_row, last_col)).Select` Then just use rng in your code where you have the `Range(VAR0:VAR1)` – rryanp Nov 06 '17 at 03:39
  • I will try that, thank you a lot! I will assign the selections to LC and LR and the do:ActiveSheet.ListObjects.Add(xlSrcRange, Range(ActiveCell, Cells(LR, LC)), , xlYes).Name = _ "Table0" – 10110 Nov 06 '17 at 03:44
0

Basically, this is what I was looking for, maybe in the future someone will stumble upon this and use it. 

'----------+ Format Raw Data As Table +----------
Dim table0 As ListObject
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Set table0 = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
tableb0.TableStyle = "TableStyleMedium15"
'----------+ Done Formatting as Table +-----------
'Start by declaring a variable as a ListObject, 
'Goto cell A1
'Select All the way from top to bottom till last cell with data
'Select All the way from left to right till last cell with data
'set table0 ListObject to the selection we just made
'finally format table0 by accessing table style "property"
'This may not work if data has blank cells.
'Specially, if A rows / columns have rows or data is not uniform.

*Credits to @rryanp for helping me and this code actually belongs to @Dmitry Pavliv from this answer to another question.

10110
  • 2,353
  • 1
  • 21
  • 37