Copy Occupied cells within a range and Paste to a different cell

[cbye]

Hi, I get reports that we need to move certain cells around to make them more readable. The Header is in Row4. The occupied cells are somewhere between A5 and A300, each report is different. I need to copy or cut whichever cell is occupied and paste it one column over and one row down. IE CellA4 is hostname and CellB4 is Machine Type. I don't want these to move. Cell A5 is hostnameA. This needs to move to B6, right under the machine type. They are spaced random within A5 and A300 but they always will be pasted one column over and one row down. I am fairly new to VBA and have tried several Range commands but I always seem to copy all the cells and when I paste them in colB, they write over the existing data. Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could we have a small set of sample dummy data and then the expected results with XL2BB?

 

[cbye]

Book1
	A	B	C	D	E	F
4	Hostname	Machine Type	Machine data A	Machine data B	Machine data C	Machine data D
5	hoatnameA	1234	data	data	data	data
6			data	data	data	data
7			data	data	data	data
8			data	data	data	data
9			data	data	data	data
10			data	data	data	data
11
12	hostnameB	2000	data	data	data	data
13			data	data	data	data
14			data	data	data	data
15
16	hostnameC	4567	data	data	data	data
17			data	data	data	data
18			data	data	data	data
19			data	data	data	data
20			data	data	data	data
21			data	data	data	data
22			data	data	data	data
23			data	data	data	data
24			data	data	data	data
25			data	data	data	data
26			data	data	data	data
27			data	data	data	data
28			data	data	data	data
29			data	data	data	data
30			data	data	data	data
31			data	data	data	data
32			data	data	data	data
33			data	data	data	data
Sheet1

Book1
	A	B	C	D	E	F
4	Hostname	Machine Type	Machine data A	Machine data B	Machine data C	Machine data D
5		1234	data	data	data	data
6		hoatnameA	data	data	data	data
7			data	data	data	data
8			data	data	data	data
9			data	data	data	data
10			data	data	data	data
11
12		2000	data	data	data	data
13		hostnameB	data	data	data	data
14			data	data	data	data
15
16		4567	data	data	data	data
17		hostnameC	data	data	data	data
18			data	data	data	data
19			data	data	data	data
20			data	data	data	data
21			data	data	data	data
22			data	data	data	data
23			data	data	data	data
24			data	data	data	data
25			data	data	data	data
26			data	data	data	data
27			data	data	data	data
28			data	data	data	data
29			data	data	data	data
30			data	data	data	data
31			data	data	data	data
32			data	data	data	data
33			data	data	data	data
Sheet1

 

[cbye]

I don't think I mentioned this in the inital post. Each report is different. IE some reports only have 1 hostname. Some have up to 15. And they could be anywhere between A4 and A300.

 

[Peter_SSs]

Thanks for the samples.

Try this with a copy of your workbook.

Sub MoveHostName()
  Dim r As Range
  
  For Each r In Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    r.Cut Destination:=r.Offset(1, 1)
  Next r
End Sub

 

[cbye]

AAAAAAAAAANd thats why you make the big bucks Peter_SSs!!! I appreciate you! Works like a champ. Gunna save me tons of time. VBA, work smart not hard yea? Thanks Again.

 

[Peter_SSs]

? You're welcome. Thanks for the follow-up.

 

[cbye]

Hi Peter_SSs. Im reaching out to you re the code you made for me previously. I have a very similar situation that I need to move a cell's contents if occupied to a specific cell. Much like your other code going over 1 column and down 1 row. I have some data that needs to move from column H to column A and then down 3 rows. IE data is in H5, end result would be A8. I thought I had a grasp on what you did but when I made changes, it didn't work. This is what I have.

Sub MoveUAKdate()
  Dim r As Range
 
  For Each r In Range("H5", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    r.Cut Destination:=r.Offset(-7, 3)
  Next r
  
 
End Sub

I get a runtime error 1004 and when I debug it highlights " r.Cut Destination:=r.Offset(-7, 3)"

It works if I have positive numbers for the offset. How do I move backwards with this? All of the references that I found always moved the data in a positive direction.

 

[Peter_SSs]

When posting code in the forum, please use the available code tags to preserve the code formatting.
I have fixed that for you in the above post. My signature block below has more details.

In Offset, the first argument is rows and the second is columns so you just have your numbers the wrong way around.
It should be

r.Cut Destination:=r.Offset(3, -7)

 

[cbye]

Ah, ok got it. Works great. Ill remember to use the code tags. Thanks again.