Macro or VBA code to determine a rang

Microsoft Office excel 2007 home & stude...
July 8, 2010 at 00:56:41
Specs: Windows XP
Hi,
can you please help me with a Macro or VBA code to determine a range.
I want to create a table on different sheets.
The Macro shows me:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$30"), , xlYes).Name = _
"Table6"

But probably the next time you have more or less rows.
It will always start at cell A1 but the rows can be different.
How can I tell the system the range is from A1 to the last row and last column where the cells are not empty?
In this case it is for a table but I also need it for deleting all cells as from A2 till .... the last row or last column where cells are not empty.
Thanks for your advise.


See More: Macro or VBA code to determine a rang

Report •

#1
July 8, 2010 at 04:10:19
re: "How can I tell the system the range is from A1 to the last row and last column where the cells are not empty?"

These 2 lines will find the last column and last row with data:

  
LastColumn = ActiveSheet.UsedRange.Column - 1 + _
             ActiveSheet.UsedRange.Columns.Count

LastRow = ActiveSheet.UsedRange.Row - 1 + _
          ActiveSheet.UsedRange.Rows.Count

Then you could something like this to Select A1 through the last row/column:

ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select


Report •

#2
July 8, 2010 at 05:21:28
Thank you the range is working now. I'm able to select the range. Only the create table seems not to work. FYI I know the column name.
I tried:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$" & LastRow), , xlYes).Name = "TableAsia"

Is it maybe because I give a Name to the table? as in "TableAsia"
thanks again


Report •

#3
July 8, 2010 at 09:55:26
Hi,

I used this:

Sub AddTable()

LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$" & LastRow), , xlYes).Name = "TableAsia"
End Sub

and it worked.
Then I ran this:
MsgBox ActiveSheet.ListObjects(1).Name
which returned "TableAsia", so the name is not the problem.

Is it possible that the data range A1 to Gx was not on the ActiveSheet at the time the Macro was run.

If the data for the table is on a Worksheet named "Tables" then use:

Sub AddTable()
LastRow = Worksheets("Tables").UsedRange.Row - 1 + Worksheets("Tables").UsedRange.Rows.Count
Worksheets("Tables").ListObjects.Add(xlSrcRange, Range("$A$1:$G$" & LastRow), , xlYes).Name = "TableAsia"
End Sub

Regards


Report •

Related Solutions

#4
July 8, 2010 at 11:06:50
Narrowing it down even farther, I used:

Sub PasteFromPost()
LastRow = 10
ActiveSheet.ListObjects.Add(xlSrcRange, _
     Range("$A$1:$G$" & LastRow), , xlYes).Name = "TableAsia"
End Sub

Where the Table Creation line was copied/pasted directly from your post to check for typos.

No problems here!



Report •

#5
July 9, 2010 at 01:56:54
Thank you both for your help.

It would be nice if I could be your student. Have so many more questions.
Like why are codes working when I put them in a macro, and which are translated in the VBA Project in a Module and when I put them as CommandButton in an Object, nothing is happening.
anyway. Untill now I'm Happy with the result.

Thanks from an happy student


Report •

Ask Question