VBA - Dynamic table size

Carlos Ruano · Mar 7, 2017

Hi,

I recorded a macro, and everything is working perfectly, except one thing! I have several sheets in a workbook that have data in columns A : P. The number of rows with data will vary each time that I run this macro. So, I might run it today with data in Sheet1 from A1 : P100, but tomorrow Sheet1 might have data from A1 : P200. How can I solve this problem?

This is a sample of what I have right now for three sheets from recording the macro:

Sheets("Albumin").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table9"
Range("Table9[#All]").Select
Sheets("ALP").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table10"
Range("Table10[#All]").Select
Sheets("ALT").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table11"
Range("Table11[#All]").Select

Thank you for any help!!

Carlos

Marc L · Mar 7, 2017

Hi !

See CurrentRegion, UsedRange, … in VBA inner help.

Monty · Mar 7, 2017

Hello Carlos.

You can also try finding last row of the table to perform your action as you said table size is dynamic..

Try this
i have taken your one line of code.

Code:

Sub Test()
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Albumin").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table9"
Range("Table9[#All]").Select
End Sub

Carlos Ruano · Mar 7, 2017

Marc L said:
Hi !

See CurrentRegion, UsedRange, … in VBA inner help.

Thank you, Marc. I am not even sure what "VBA inner help" is

I did a quick look around the site, and didn't see an area by that name. I will keep looking!

Marc L · Mar 7, 2017

The VBA help which is directly inside Excel in its VBE side !
Like mouse cursor on a code statement you hit F1 key …

More than 50% of VBA forum questions can be solved
just by reading VBA help !

Carlos Ruano · Mar 7, 2017

Monty said:
Hello Carlos.

You can also try finding last row of the table to perform your action as you said table size is dynamic..

Try this
i have taken your one line of code.

Code:

Sub Test() Dim lrow As Long lrow = Range("A" & Rows.Count).End(xlUp).Row Sheets("Albumin").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table9" Range("Table9[#All]").Select End Sub

Thank you Monty!

I tried replacing my code with yours, but it did not work as expected. Now, I should inform you that I know next to nothing about VBA, but am trying to learn.

Let me explain two changes that I made to your code, and then you can tell me if I am an idiot

First, I have several macros running in sequence, and the first one has "Dim lr As Long", so I replaced your "Dim lrow As Long" with the one I already had since not doing so has caused me problems in the past. Was this not a good move?

Second, instead of trying your code on sheet: Albumin, I did so with a different sheet (Finely Granular Cast). This is just the sheet that I chose to test with since I have two sources of this data with different numbers of rows.

So, I went from this:

Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$3"), , xlYes).Name = _
"Table22"
Range("Table22[#All]").Select

To this:

lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

Can you see why your code is not working for me?

Thank you!

Carlos Ruano · Mar 7, 2017

Marc L said:
The VBA help which is directly inside Excel in its VBE side !
Like mouse cursor on a code statement you hit F1 key …

More than 50% of VBA forum questions can be solved
just by reading VBA help !

Oh Jeez ... now I feel like an idiot. Thank you.

Monty · Mar 7, 2017

Code:

YOUR CODE
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

MY CODE
Declared variable as lr but using lrow .

lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

Carlos Ruano · Mar 7, 2017

Monty said:

Code:

YOUR CODE
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

MY CODE
Declared variable as lr but using lrow .

lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

It looks like this is working now!! I have to run for an hour or so, but I will do more extensive testing once I return and let you know how it goes.

THANK YOU!!

Carlos Ruano · Mar 7, 2017

Monty said:

Code:

YOUR CODE
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

MY CODE
Declared variable as lr but using lrow .

lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

I am back, and I can confirm that your code works perfectly. I am having a bit of an issue with the "Finely Granular Cast" sheet, but it must be due to something that I inadvertently did with the source data for that particular test. All other sheets are working!

Thank you, Monty!

And Marc, thank you for pointing out the obvious. I never clicked on the help icon in the VBA window, but now I have, and it looks like it will be vastly more valuable to me than 'help' functions in other applications have been.

Carlos Ruano · Mar 8, 2017

Monty said:

Code:

YOUR CODE
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

MY CODE
Declared variable as lr but using lrow .

lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Finely Granular Cast").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$" & lrow), , xlYes).Name = "Table22"
Range("Table22[#All]").Select

Hi Monty,

The code that you provided is working perfectly, however, now I have a different issue. When I try to run the macro, I get the following error: "Compile error: Procedure too large".

I looked at the rest of my code, and I think I have identified an area that could probably be shrunk. Is there any way to shrink the following code (used to hide specific sheets)?

Sheets("Abs. Basophils").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Abs. Eosinophils").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Abs. Lymphocytes").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Abs. Monocytes").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Abs. Neutrophils").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Amorph. Urate Crystals").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Amorphous Phos. Crystals").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Bacteria").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Bilirubin - Urine").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Calcium Oxalate Crystals").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Fasting Status").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("hCG, Quant.").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("HCT").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Hematology Slide").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Leukocytes").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Nitrites").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Specific Gravity").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Squamous Epithelial Cells").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Uric Acid Crystals").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Urine Culture and Sensitivity").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Visit Type").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("WBC Clumps, Urine").Select
ActiveWindow.SelectedSheets.Visible = False

Monty · Mar 8, 2017

Carlos Ruano

Can i have sample file to complete your job...code you have provided is called bad code in VBA...let me clean it.

Monty · Mar 8, 2017

Hello Carlos Ruano

Please find attatched sample file created for you..amend accordingly in your file.

just mention in the main sheet, Sheets required to hide and click the button..

Let me know any challenges.

Carlos Ruano · Mar 8, 2017

Monty said:
Carlos Ruano

Can i have sample file to complete your job...code you have provided is called bad code in VBA...let me clean it.

Hi Monty, I am happy to send you a sample file. I am sure the code is terrible, as it was mostly done by recording my actions. I have also added some code that I found from the web, and other code that kind people like yourself have provided

Interestingly, the code that you provided yesterday to create tables is now acting funny (the tables are longer than they need to be ... blank rows). I am sure this is caused by some other change that I have made.

I will supply two files: Source data and macro template. The macro in the template is supposed to, in order:

1. Create one new sheet for each "Test Name" in column K, and then paste the corresponding data into those sheets.
2. Hide certain sheets (I think I have improved this)
3. Create tables of the data in the visible sheets
4. Add 3 blank rows at the top of every sheet (sheets created in step 1)
5. Copy reference values in "Reference Values" sheet and paste them into the top three rows of individual sheets. Note, the reference values in the source data are not always the reference values that are needed, hence the need for the "Reference Values" sheet.
6. Add additional columns to the right of data in each sheet, and then add my formulas. At the end, hide "Reference Values" sheet.

The source data: I just copy this data and paste it into my template, and then run the macro.

Any help or advice with any of the code would be greatly appreciated!! I just started trying to work with VBA a week or so ago, so I am completely lost. I am, however, starting to see certain things that are making sense.

Thank you!

Carlos Ruano · Mar 9, 2017

Monty said:
Hello Carlos Ruano

Please find attatched sample file created for you..amend accordingly in your file.

just mention in the main sheet, Sheets required to hide and click the button..

Let me know any challenges.

View attachment 39414

Very cool

The more I see from you and others, the less I feel I have sufficient brain power to learn VBA. I am hoping that one day, it will all just click! We'll see LOL.

I am beginning to think that perhaps the best solution for me is to break the various steps out into separate macros. I am building this template for a Project Manager whom I support. She is an awesome lady, and I wanted to make her life as easy as possible, but I might be over my head.

Thanks

VBA - Dynamic table size

Carlos Ruano

Member

Marc L

Excel Ninja

Monty

Well-Known Member

Carlos Ruano

Member

Marc L

Excel Ninja

Carlos Ruano

Member

Carlos Ruano

Member

Monty

Well-Known Member

Carlos Ruano

Member

Carlos Ruano

Member

Carlos Ruano

Member

Monty

Well-Known Member

Monty

Well-Known Member

Attachments

Carlos Ruano

Member

Attachments

Carlos Ruano

Member