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

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