PDA

View Full Version : Start Range down 1 Row



YellowLabPro
04-05-2007, 08:31 AM
In the following code I want to populate the range beginning at "A2". The only way I have been able to do this is by inserting a row. This is not ideal due to other things that occur w/ the data in ongoing use.

This the section of code I need help in editing to begin the data in "A2"
For Each R In WsS.Range("A4", WsS.Cells(Rows.Count, "A").End(xlUp).Address)
If R.Text = "" Then
WsT.Rows(LRowT & ":" & LRowT + 1).Value = WsS.Rows(R.Row - 1 & ":" & R.Row).Value
LRowT = LRowT + 2
End If


Full Code:

Option Explicit
Sub CopyRowBlankCell()
Dim LRowS As Long, LRowT As Long
Dim R As Range
Dim WsS As Worksheet, WsT As Worksheet

Set WsS = Sheets("PivotTable")
Set WsT = Sheets("Correct")
LRowS = WsS.Cells(Rows.Count, "H").End(xlUp).Row + 1
LRowT = WsT.Cells(Rows.Count, "H").End(xlUp).Row

'WsT.Range("A2:K" & LRowT).ClearContents
'Range("A2:H310").Select
'Selection.ClearContents

For Each R In WsS.Range("A4", WsS.Cells(Rows.Count, "A").End(xlUp).Address)
If R.Text = "" Then
WsT.Rows(LRowT & ":" & LRowT + 1).Value = WsS.Rows(R.Row - 1 & ":" & R.Row).Value
LRowT = LRowT + 2
End If
Next R
'WsT.Rows("1:1").EntireRow.Insert
Range("A1:H1") = Array("Item#", "~Records", "Dept.", "Cat.", "Price", "Fairfax", "VaBeach", "Total")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit

Sheets("Correct").Select
[A1].Activate
End Sub


Thanks,

YLP

YellowLabPro
04-05-2007, 09:18 AM
I have played around w/ moving this earlier in the code:


WsT.Range("A1:H1") = Array("Item#", "~Records", "Dept.", "Cat.", "Price", "Fairfax", "VaBeach", "Total")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit

But it continues to be overwritten by the For If loop. Running it a second time puts the array in the correct row, but overwrites the top value of the loop.
Not good.

xld
04-05-2007, 10:12 AM
I am not getting te picture YLP, as to what you are trying to do. And you mention A2, but all I see is A4.

YellowLabPro
04-05-2007, 10:21 AM
Hi Bob,
I will upload the workbook. There are three sheets, Data Source- "DataEdited", Pivot Table- "Pivot Table" and Target Sheet- "Correct".

The idea is for the pivot table to combine the records from the data source to locate problems. Then the target sheet is a subset of data from the pivot table.

The problem is that the code copies over these records but starts them in the lastrow, which seems to be row 1 the first time running the code, then run the code a second time and the headers get written in and overwrite the first data record.

I am working on scaling down the workbook to upload.

thx,
YLP

YellowLabPro
04-05-2007, 10:48 AM
The third sheet "Correct" is ready to run the code.
These two records should be in the first two positions. The top one is being overwritten by the headers. My thought is to have the values begin on A2 of "Correct". A4 is the source cell referrence on the source sheet.

Norie
04-05-2007, 11:49 AM
YLP

Why a pivot table?

YellowLabPro
04-05-2007, 12:00 PM
See post# 4



The idea is for the pivot table to combine the records from the data source to locate problems. Then the target sheet is a subset of data from the pivot table.

Norie
04-05-2007, 12:40 PM
Doug

Well that really isn't what pivot tables are for normally.

If you want to get a subset of data then I would say that using filters would probably be the more sensible.

Also, you still haven't explained what you are doing.

This is your data, so we don't really know what 'problems' you might be trying to find.

YellowLabPro
04-05-2007, 12:47 PM
Norie,
Sure I have, I have even put it in a worksheet so if someone is interested or inclined they can download it and view it in case I have not presented the question well.
Regardless if that is what Pivot Tables were designed to do, this is a great by product of them... it shows where there are data errors.
The Pivot Table is not really the issue, I am just showing the linear path. I can make the data static, but that will not change where the data is being populated on the target sheet. The issue is on the Target sheet and how I can populate the sheet beginning on row 2 so my headers remain visible.

lucas
04-05-2007, 12:59 PM
LRowS = WsS.Cells(Rows.Count, "H").End(xlUp).Row + 1
LRowT = WsT.Cells(Rows.Count, "H").End(xlUp).Row + 1

YellowLabPro
04-05-2007, 11:00 PM
Lucas,
Thanks! I don't understand how that worked.... I am trying to read the code to understand what is happening w/ the + 1...
If you can shed some light please?

Thanks,

YLP

mdmackillop
04-06-2007, 12:27 AM
WsS.Cells(Rows.Count, "H").End(xlUp).Row returns the row number of the last used cell in column H of the specified sheet. "+1" simply increases this number by 1
It's exactly the same as using
WsS.Cells(Rows.Count, "H").End(xlUp).Offset(1).Row

YellowLabPro
04-06-2007, 03:59 AM
MD,
Let me see if I understand:
LRowS = WsS.Cells(Rows.Count, "H").End(xlUp).Row + 1
LRowT = WsT.Cells(Rows.Count, "H").End(xlUp).Row + 1

.Cells returns a property, yes? If so, what is the property?

Rows.Count, "H").End(xlUp) begins at the last cell in Col. H, "H65536", and finds the last cell in "H" counts starting here up to row 1? Or does it count till it finds a blank cell?

.Row + 1, I want to inquire about after you fill me in on the top two questions?

thx,

YellowLabPro
04-06-2007, 05:38 AM
The following code now populates the sheet with all the desired records, Thanks Lucas.

There is still an issue which I do not comprehend. Partially due to the immediate post not understanding exactly what the code is doing.

But for here, the data is cleared and then new data is populated, but it is ending up below where the last data was cleared.
So if the initial data filled rows 2-35, then the old data is being cleared with ClearContents line of code, the new data begins populating at row 36. Rows 2-35 are blank now and data is in 36-70 and so on.
If I manually clear the data, then data begins populating in row 2, where it is desired.
I realize it is something in this section, I just cannot decipher the code well enough to know what is going on here:

For Each R In WsS.Range("A4", WsS.Cells(Rows.Count, "A").End(xlUp).Address)
If R.Text = "" Then
WsT.Rows(LRowT & ":" & LRowT + 1).Value = WsS.Rows(R.Row - 1 & ":" & R.Row).Value 'Old Way
LRowT = LRowT + 2
End If
Next R


Entire program:

Option Explicit
Sub CopyRowBlankCell()
Dim LRowS As Long, LRowT As Long
Dim R As Range
Dim WsS As Worksheet, WsT As Worksheet

Set WsS = Sheets("PivotTable")
Set WsT = Sheets("Correct")
LRowS = WsS.Cells(Rows.Count, "H").End(xlUp).Row + 1
LRowT = WsT.Cells(Rows.Count, "H").End(xlUp).Row + 1
'WsT.Range("A2:k" & LRowT + 1).ClearContents
Range("A2:J310").ClearContents

For Each R In WsS.Range("A4", WsS.Cells(Rows.Count, "A").End(xlUp).Address)
If R.Text = "" Then
WsT.Rows(LRowT & ":" & LRowT + 1).Value = WsS.Rows(R.Row - 1 & ":" & R.Row).Value 'Old Way
LRowT = LRowT + 2
End If
Next R
Range("A1:H1") = Array("Item#", "~Records", "Dept.", "Cat.", "Price", "Fairfax", "VaBeach", "Total")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Sheets("Correct").Select
[A1].Activate
End Sub



thx,

YLP

Norie
04-06-2007, 05:40 AM
Doug

As far as I'm aware you haven't fully explained what you are doing.

And I've read most of your threads, downloaded most of your attachments and also posted to try and help.

I can't see anywhere in this thread or in your latest attachment you state clearly how you are defining a problem with the data.

What I mean is something simple like If X then Y.

lucas
04-06-2007, 06:37 AM
Not sure YLP....maybe your missing some of the data...maybe clear all contents:
WsT.Cells.ClearContents

YellowLabPro
04-06-2007, 07:29 AM
Thanks Lucas,
I think I have got it figured out.
The variable LRwoT = WsT.Cells(Rows.Count, "H").End(xlUp).Row + 1
finds the correct range. The variable is holding this range in memory after clearing the data. Upon repopulating the sheet w/ fresh data, the variable still thinks this is a used range and hence places the data below that range.
I placed the same line below ClearContents and it runs as desired.


YLP

lucas
04-06-2007, 07:32 AM
I was just looking at this and had come to the same conclusion.....glad you got it hashed out.....

YellowLabPro
04-06-2007, 09:01 AM
Could I have written my code differently to avoid this, or is this a standard way of handling a situation like this?

lucas
04-06-2007, 09:33 AM
as things progress we sometimes have to adjust to what exists as opposed to rewritting the whole thing.....I don't see any problems with it...but others may have input for you.