Clueless about using cut and copy with offset
 

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Just blank, deleting will misalign the rest of the spreadsheet

"Thomas [PBD]" wrote:

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Jeremiah,

What I meant was that once the cutting is placed into the correct line the
line the data came from would be blank. Would you like it deleted. I wrote
this quick macro here where you can remove the ' in front of the delete
statement if you wish to use it.

Public Sub Cut_Offset()
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).Value = "Date" Then
If Cells(x, 3).Value = "" Then
Range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub

This will count the number of rows that say "Date" in Column B, copy the row
below it from C:O and place it the row above it. Optional delete blank row.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

Just blank, deleting will misalign the rest of the spreadsheet

"Thomas [PBD]" wrote:

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

If the value in Column B = "Date" as a string would
be one thing, but if it = Date as a constant for today's
date then it another thing. But going with what you posted,
Then:

Sub cut_cpy()
Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
With ActiveSheet
If Range("B" & i) = "Date" And Range("C" & i & ":O" & i) _
.SpecialCells(xlCellTypeBlanks).Count = 13 Then
Range("C" & i + 1 & ":O" & i + 1).Cut _
Destination:=Range("C" & i)
End If
End With
Next
End Sub

"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Yes, the values can be deleted...Sorry, I maybe misunderstood there are too
many ways to say the same thing.
"Thomas [PBD]" wrote:

Jeremiah,

What I meant was that once the cutting is placed into the correct line the
line the data came from would be blank. Would you like it deleted. I wrote
this quick macro here where you can remove the ' in front of the delete
statement if you wish to use it.

Public Sub Cut_Offset()
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).Value = "Date" Then
If Cells(x, 3).Value = "" Then
Range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub

This will count the number of rows that say "Date" in Column B, copy the row
below it from C:O and place it the row above it. Optional delete blank row.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

Just blank, deleting will misalign the rest of the spreadsheet

"Thomas [PBD]" wrote:

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Yes, it is a string. "Date" being the row heading. I ran this and got a run
time error sayting no cells were found.

"JLGWhiz" wrote:

If the value in Column B = "Date" as a string would
be one thing, but if it = Date as a constant for today's
date then it another thing. But going with what you posted,
Then:

Sub cut_cpy()
Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
With ActiveSheet
If Range("B" & i) = "Date" And Range("C" & i & ":O" & i) _
.SpecialCells(xlCellTypeBlanks).Count = 13 Then
Range("C" & i + 1 & ":O" & i + 1).Cut _
Destination:=Range("C" & i)
End If
End With
Next
End Sub

"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Three reasons why it would not find the cells.
1. Wrong sheet is active.
2. "Date" is not in column B.
3. There are no rows where All cells in Col C:O are blank.

The macro worked in a test set up.

"jeremiah" wrote:

Yes, it is a string. "Date" being the row heading. I ran this and got a run
time error sayting no cells were found.

"JLGWhiz" wrote:

If the value in Column B = "Date" as a string would
be one thing, but if it = Date as a constant for today's
date then it another thing. But going with what you posted,
Then:

Sub cut_cpy()
Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
With ActiveSheet
If Range("B" & i) = "Date" And Range("C" & i & ":O" & i) _
.SpecialCells(xlCellTypeBlanks).Count = 13 Then
Range("C" & i + 1 & ":O" & i + 1).Cut _
Destination:=Range("C" & i)
End If
End With
Next
End Sub

"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

I have checked and double checked and my case does not fit any of the 3
possibilities, I am looking at the correct sheet and column b does say Date
and there are rows where C through O are blank. Could it be there are spaces
behind the word Date (I tried with * and without), or perhaps because the
entire rows font and interior colors are conditionally formatted it is does
not appear the cells are blank?

"JLGWhiz" wrote:

Three reasons why it would not find the cells.
1. Wrong sheet is active.
2. "Date" is not in column B.
3. There are no rows where All cells in Col C:O are blank.

The macro worked in a test set up.

"jeremiah" wrote:

Yes, it is a string. "Date" being the row heading. I ran this and got a run
time error sayting no cells were found.

"JLGWhiz" wrote:

If the value in Column B = "Date" as a string would
be one thing, but if it = Date as a constant for today's
date then it another thing. But going with what you posted,
Then:

Sub cut_cpy()
Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
With ActiveSheet
If Range("B" & i) = "Date" And Range("C" & i & ":O" & i) _
.SpecialCells(xlCellTypeBlanks).Count = 13 Then
Range("C" & i + 1 & ":O" & i + 1).Cut _
Destination:=Range("C" & i)
End If
End With
Next
End Sub

"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

I had to define some variables and not really confident in that task but have
this working. Problem is it stops abruptly at row 4051. There are a total
of 4588 rows in the spreadsheet and will grow each week. No obvious reason
staring me in the face that it would stop but perhaps my variables aren't
correct? Like I said, not vb educated so I may have done something
incorrectly.

Public Sub Cut_Offset()
Dim y As Single
Dim x As Single
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).value = "Date" Then
If Cells(x, 3).value = "" Then
range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub


"Thomas [PBD]" wrote:

Jeremiah,

What I meant was that once the cutting is placed into the correct line the
line the data came from would be blank. Would you like it deleted. I wrote
this quick macro here where you can remove the ' in front of the delete
statement if you wish to use it.

Public Sub Cut_Offset()
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).Value = "Date" Then
If Cells(x, 3).Value = "" Then
Range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub

This will count the number of rows that say "Date" in Column B, copy the row
below it from C:O and place it the row above it. Optional delete blank row.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

Just blank, deleting will misalign the rest of the spreadsheet

"Thomas [PBD]" wrote:

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881

Clueless about using cut and copy with offset
 

Jeremiah,

I know the issue with this. The count is counting nonblank cells, so it is
counting that there are 4051 nonblanks, and 537 blank cells. You have two
options. Either delete the blank rows after Cutting the data, or setting the
reference to look at all rows regardless of blanks.

A) take the comment mark out of the Delete statement (')
B) y = Excel.WorksheetFunction.CountA(Columns("B:B")) +
Excel.WorksheetFunction.CountBlank(Columns("B:B"))

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"jeremiah" wrote:

I had to define some variables and not really confident in that task but have
this working. Problem is it stops abruptly at row 4051. There are a total
of 4588 rows in the spreadsheet and will grow each week. No obvious reason
staring me in the face that it would stop but perhaps my variables aren't
correct? Like I said, not vb educated so I may have done something
incorrectly.

Public Sub Cut_Offset()
Dim y As Single
Dim x As Single
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).value = "Date" Then
If Cells(x, 3).value = "" Then
range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub


"Thomas [PBD]" wrote:

Jeremiah,

What I meant was that once the cutting is placed into the correct line the
line the data came from would be blank. Would you like it deleted. I wrote
this quick macro here where you can remove the ' in front of the delete
statement if you wish to use it.

Public Sub Cut_Offset()
y = Excel.WorksheetFunction.CountA(Columns("B:B"))

For x = 1 To y
If Cells(x, 2).Value = "Date" Then
If Cells(x, 3).Value = "" Then
Range(Cells(x + 1, 3).Address & ":" & Cells(x + 1, 15).Address).Cut
Cells(x, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Rows(x + 1).Delete ' Delete Statement
End If
End If
Next
End Sub

This will count the number of rows that say "Date" in Column B, copy the row
below it from C:O and place it the row above it. Optional delete blank row.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

Just blank, deleting will misalign the rest of the spreadsheet

"Thomas [PBD]" wrote:

Jeremiah,

Would you like the row that you are cutting from to be deleted once the
process is completed (as it should be blank)?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"jeremiah" wrote:

I am trying to figure out the following....If the value of column B = "Date"
and columns C:O are empty, cut and copy the values from columns C:O in the
row directly below. Example below, the numbers in the row need to line up
with Date. The file was an imported csv file so sometimes the rows line up
and other times they don't.

Date
493,352 102.98 4,791 30.88 58,072 1,881