row count for tables

kalim

Board Regular
Joined
Nov 17, 2010
Messages
87
Hi excel users.

I have a table set up as a template where people can enter data as they need. Therefore, the row count will consistently change.

There are two things I need to happen.

1) I have the following VBA code to copy a value into the cells.
Code:
Sub Copy_all()
 
Dim LastRow As Long
 
Range("E5:E35").ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Range("E5:E" & LastRow).Value = Range("E3")
 
End Sub

This works fine for cells but when I put this code in a table, the values get entered to the end of the table (including cells that were blanks in column b). What I need to happen is that the values only get entered in column E if there is text (not blanks) in cell b of that row.

2) I need to do the same thing for columns F:L. So instead of writing a macro for each, can it be done in one macro. Similarly can it be done so I can add this macro to a button for each column, so they work individually and not all at once. Meaning column E would be assigned to Shapes("Button 1"), column F Button 2 etc. And if Button 1 is clicked only that column is changed.

FYI, the following will change for each column - column F would look like

Code:
Range("f5:f35").ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Range("f5:f" & LastRow).Value = Range("f3")

Thanks.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Does this code make any sense for you?

Code:
Sub Copy_all()
Dim LastRow As Long
Range("E5:E35").ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Set Rng = Range("B5:B" & LastRow)
For Each C In Rng
    If C<> "" Then C.Offset(, 3).Value = Range("E3")
Next C
End Sub
Excel Workbook
BCDEF
3999
4Column1Column2Column3Column4Column5
5322
6321999321
7654999654
8321999321
9222
10111
11333999333
12444999444
13555999555
Sheet1
Excel 2007
 
Upvote 0
Thanks for the reply.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
jim may - I just tried it and yes it make sense thanks - it works well.<o:p></o:p>
<o:p></o:p>
That fixes my first problem.<o:p></o:p>
<o:p></o:p>
For my second problem - instead of reproducing that same vba code another 7 times (for columns F:L), is it possible to do it all in one?<o:p></o:p>
<o:p></o:p>
Thanks.<o:p></o:p>
 
Upvote 0
Why not post a sufficient amt of your table so we can see the layout, a picture is worth a thousand words.
 
Upvote 0
Thanks for the continued support.

Here is a portion of the layout/table
cells E5:L7 are data valadtion cells.
E3:L3 - will contain the value that changes all the values of that column.
E2:L2 - will contain the button that each macro will be attached to.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 26px"><COL style="WIDTH: 86px"><COL style="WIDTH: 99px"><COL style="WIDTH: 20px"><COL style="WIDTH: 98px"><COL style="WIDTH: 97px"><COL style="WIDTH: 104px"><COL style="WIDTH: 82px"><COL style="WIDTH: 75px"><COL style="WIDTH: 76px"><COL style="WIDTH: 79px"><COL style="WIDTH: 98px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD><TD style="COLOR: #ffff00; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 12pt">No</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Client</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">id</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt; FONT-WEIGHT: bold">ref</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Jan</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Feb</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Mar</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Apr</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">May</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">June</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">July</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Aug</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 12pt">1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">name1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">xxxx-xxx</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">g1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 17</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 8</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 7</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 4</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 12pt">2</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">name2</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">xxxx-xxx</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">g1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 15</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 5</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 4</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 7</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 5</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 16</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 12pt">3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">name3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">xxxx-xxx</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">g3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 18</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 4</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 6</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt">part 1</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Does this help?
 
Upvote 0
If I understand what's going on here, Without the use of Buttons you could paste this code into your Sheet Module then all you have to do is Double-Click on Row 4 (Any Column between E and L and your goals should be met.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim LastRow As Long
If Target.Row <> 4 Or Target.Column < 5 Or Target.Column > 12 Then Exit Sub
Range(Cells(Target.Offset(1).Row, Target.Column), Cells(35, Target.Column)).ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Set Rng = Range("B5:B" & LastRow)
For Each C In Rng
    If C <> "" Then C.Offset(, Target.Column - 2).Value = Target.Offset(-1)
Next C
End Sub
 
Upvote 0
Thanks again for the help jim may.

Yes you did understand correctly and it does do what I need.
I will just to have to adjust the template as buttons can't be used.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top