Excel Sheet

[DMT Dave]

Hi Guy's, is there any way i can stop the bold column going beyond the record quantity ?

The column is currently going way beyond for example if there are 37 records, i would like the word "yes" to stop at 37, it currently starts at Cell 3,10 now goes down thousands of cells

.Worksheets(1).Cells(2, 1) = "PLEASE REPLACE 'YES' "
.Worksheets(1).Cells(3, 1) = "WITH 'N' IF ON HOLD"
.Worksheets(1).Cells(1, 3) = "SHIPMENT DATE"
.Worksheets(1).Cells(1, 4) = "DEL TO"
.Worksheets(1).Cells(1, 5) = "SL NUMBER"
.Worksheets(1).Cells(1, 6) = "PO-NUMBER"
.Worksheets(1).Cells(1, 7) = "PRODUCT TYPE"
.Worksheets(1).Cells(1, 8) = "PRODUCT DETAILS"
.Worksheets(1).Cells(1, 9) = "STATUS"
.Worksheets(1).Columns("J:J") = "Yes" continues way beyond the amount of records in recordset
.Worksheets(1).Cells(1, 10) = "CAN SHIP YES"
.Worksheets(1).Cells(2, 10) = ""
.Worksheets(1).Cells(3, 3).CopyFromRecordset rs
.Worksheets(1).Range("C3:K3").Borders(xlEdgeTop).L ineStyle = xlContinuous
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignm ent = xlLeft


.Worksheets(1).Columns("C:C").NumberFormat = "dd-mmm-yy"
.Worksheets(2).Cells(1, 3) = "SHIPMENT DATE"
.Worksheets(2).Cells(1, 4) = "DEL TO"
.Worksheets(2).Cells(1, 5) = "SL NUMBER"
.Worksheets(2).Cells(1, 6) = "PO-NUMBER"
.Worksheets(2).Cells(1, 7) = "PRODUCT TYPE"
.Worksheets(2).Cells(1, 8) = "PRODUCT DETAILS"
.Worksheets(2).Cells(1, 9) = "STATUS"
.Worksheets(2).Cells(1, 10) = "RELEASED"
.Worksheets(2).Cells(3, 3).CopyFromRecordset rsH
.Worksheets(2).Range("C3:J3").Borders(xlEdgeTop).L ineStyle = xlContinuous
.Worksheets(2).Cells.EntireColumn.AutoFit
.Worksheets(2).Cells.EntireColumn.HorizontalAlignm ent = xlLeft
.Worksheets(2).Columns("C:C").NumberFormat = "dd-mmm-yy"

[Homegrownandy]

.Columns("J:J") selects the whole column mate.

[Homegrownandy]

decide which column you are using to define the record count... EG column "A" will always have data if its a record. Then do a find last on column "A" for example and use this value in your VBA to decide where to stop.

If that makes sense...

https://www.excelcampus.com/vba/find...w-column-cell/

[DMT Dave]

Hi homegrownandy, thank you very much for your reply, i had a quick look and there may well be future requirements from your link, however, i tried thew following and it worked a treat for anyone else requiring similar

.Worksheets(1).Range("J3:J" & rs.RecordCount) = "Yes"

[alansidman]

you could also define the last row as a variable

Dim lr as long
lr = .Worksheets(1).Range("J" & rows.count).end(xlup).row

then

for the row in question

.Worksheets(1).range("J3:J" & lr) ="Yes"

[DMT Dave]

Hi alansidman, thank you very much, will give that try tomorrow at work,

Do I add the code in like below ?

#

.Worksheets(1).Cells(2, 1) = "PLEASE REPLACE 'YES' "
.Worksheets(1).Cells(3, 1) = "WITH 'N' IF ON HOLD"
.Worksheets(1).Cells(1, 3) = "SHIPMENT DATE"
.Worksheets(1).Cells(1, 4) = "DEL TO"
.Worksheets(1).Cells(1, 5) = "SL NUMBER"
.Worksheets(1).Cells(1, 6) = "PO-NUMBER"
.Worksheets(1).Cells(1, 7) = "PRODUCT TYPE"
.Worksheets(1).Cells(1, 8) = "PRODUCT DETAILS"
.Worksheets(1).Cells(1, 9) = "STATUS"

lr = .Worksheets(1).Range("J" & rows.count).end(xlup).row DOES THIS NEED TO BE INSIDE THE WITH WORKBOOK STATEMENT ?
.worksheets(1).Range("J3:J & lr) = "Yes"

.Worksheets(1).Cells(1, 10) = "CAN SHIP YES"#

[DMT Dave]

Hi Guys, can I add more than 1 OrderBy ina Recordset ?

And Status = '" & MyStatus & "'" & " And Source = '" & Src & "' ORDER BY DelTo;") Add another OrderBy ie: Date ??

Kind Regards

[DMT Dave]

Hi alansidman, that method didnt appear to work, there was only one "yes" in J3 !!

I did put the lr = .Worksheets(1).Range("J" & rows.count).end(xlup).row within the with workbook statement and following line was the code for row in question ??

Kind Regards

[June7]

Yes, just as in a query object, sql built with code can have multiple sort criteria, separate with a comma. If you want descending order, specify with DESC keyword.

Date is a reserved word and should not use reserved words as names for anything. If you do, enclose object names in [ ].

& "' ORDER BY DelTo, [Date];")

[ArviLaanemets]

1.Define your table as Table (Select a cell in table, from Menu select Insert>Table);
2. Select whole data part of column in Table;
3. Define Conditional Formatting for selected part of Table (NB! You have to use regular formula syntax in formula used to determine formatting condition - Conditional Formatting doesn't recognize Table syntax).

When Table column contains a single formula or formatting rule for all data cells in column, then whenever rows are added into table, the formula or formatting rule are expanded automatically.

[ArviLaanemets]

1.Define your table as Table (Select a cell in table, from Menu select Insert>Table);
2. Select whole data part of column in Table;
3. Define Conditional Formatting for selected part of Table (NB! You have to use regular formula syntax in formula used to determine formatting condition - Conditional Formatting doesn't recognize Table syntax).

When Table column contains a single formula or formatting rule for all data cells in column, then whenever rows are added into table, the formula or formatting rule are expanded automatically. And whenever something is entered into any cell immediately below last table cell, the Table is automatically expanded.