Hi, does the below work for you?
Code:Sub test() Dim lr As Long, i As Long lr = Range("A" & Rows.Count).End(xlUp).Row For i = lr To 1 Step -1 If Range("A" & i).Value = "" Then Range("A" & i).Value = Range("A" & i + 1).Value Next End Sub
I am trying to copy and paste data in column A to the blank cells above it.
For example: Cell A5 contains "1203" and I want it copied to the blank cells above A1:4
Cell A12 contains "1237" and I want it copied to the blank cells above A6:11
Cell A18 contains "1291" and I want it copied to the blank cells above A13:17
These cell reference are variable and will change depending on the data in the report. I know I need to do this with a loop (I think) but I am confusing myself trying to figure it out.
Any suggestions?
Hi, does the below work for you?
Code:Sub test() Dim lr As Long, i As Long lr = Range("A" & Rows.Count).End(xlUp).Row For i = lr To 1 Step -1 If Range("A" & i).Value = "" Then Range("A" & i).Value = Range("A" & i + 1).Value Next End Sub
Learn something new everyday.
be sure to use code tags
Code:[ code ] [ / code ] ' no spaces
Another optionCode:Sub CopyUp() With Range("A:A") .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c" .Value = .Value End With End Sub
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
It didn't work but I think it may have to do this my coding before it. In column C there are random data in cells that look like "Totals for Cost Center 0120140100 - DIRECTOR". I am trying to loop through column C to find these cells and only take the "120140100" portion of the string and copy it over to the adjacent cell in column A. One the loop was completed I want to copy and paste as above. Here is my coding:
'Set variables
Dim LastRowSH As Long
LastRowSH = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")
Dim x As Long
'Find cost centres in column C and use MID function to copy the cost centre to adjacent cell in Column A
For x = 2 To LastRowSH - 1
If Data.Cells(x, 3).Value Like "*Total*" Then
Data.Cells(x, 3) = Mid(Cells(x, 3), 25, 9).Offset(-2, 0)
End If
Next x
'Delete column C
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
'Copy cost centres to above empty cells
With Range("A:A")
.SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
.Value = .Value
End With
Maybe change
toCode:If Data.Cells(x, 3).Value Like "*Total*" Then Data.Cells(x, 3) = Mid(Cells(x, 3), 25, 9).Offset(-2, 0) End If Next x
Code:If Data.Cells(x, 3).Value Like "*Total*" Then Data.Cells(x, 1) = Mid(Cells(x, 3), 25, 9) End If Next x
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
That did not work either I was trying to avoid using text to columns but I may have to.
In what way didn't it work?
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
You need to give more description than just "did not work" in your posts.
Also change
toCode:If Data.Cells(x, 3).Value Like "*Total*" Then Data.Cells(x, 1) = Mid(Cells(x, 3), 25, 9) End If Next x
Code:If Data.Cells(x, 3).Value Like "*Total*" Then Data.Cells(x, 1) = Mid(Data.Cells(x, 3), 25, 9) End If Next x
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
I have the MID function working now. Thanks
I think I need a loop to find the value in column a (A5 for example) and paste it to the above blank cells (A1:4) and then go find the next value say A15 and paste it to the above blank cells A6:14, and so on.
The coding provided above just give me "0" in Column A
Last edited by SAMCRO2014; Nov 6th, 2018 at 01:41 PM.
works fine for meCode:With Data.Range("A:A") .SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C" .Value = .Value End With
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
Like this thread? Share it with others