please need help with my project

[Pilot5000]

Hope my question is clear and not complicated, and any help will be appreciated


I have sheet with data in columns (range "A" to "M"), the data in ranges("A"&"B") start from row 7 to row 23 down , the data in ranges("C","E" & "G”) start from row 2 downward to row 27 , the data in ranges("D" "F" &"H") start from row 10 down to row 29. the data in ranges("I" "K" &"M") start from row 4 down to row 25columns (“J” & “L”) are empty no data .

The problem is that every week the information can be different in 3 aspects

1. 1. the numbers of columns with data
      
      
   2. location of the starting data in the column downward
      
      
   3. location of the ending data in the column
      
      i.e some week the data can be from range A to W, when in ranges (A&B) start from row 4 downward till row 18 , and range (("C","E" & "G”) start from row 5 downward till row 25,ranges ( D &H) empty and so on
      
      
   4. the only thing that remain the same that every all data in every week start in column A
      
      I need a macro that check every column (starting column A1-downword) find the first row and the last row with data in that column and move the data to range A2-downword and do the same in each column across the sheet until the last column with data so all data across is located from row 2 downward (A2,B2,C2 ….. and so on till the last column with data ). Furthermore, please do not forget the empty columns, in the loop. The empty columns can stay the same because I any way I need to insert empty columns between each two column with data and I already have the macro to that.
      
      thank to everyone in advance for any assistance if you need more clarification please do not heisted to ask
      
      
      Mike.M
      

 

[Hui]

Mike

try this code

Sub Shift_Data()
Dim c As Range
For Each c In Range("a1:n1")
  If c.End(xlDown).Row < 1000 Then
  Range(c.End(xlDown), c.End(xlDown).End(xlDown)).Cut Cells(2, c.Column)
  End If
Next

End Sub

 

[Pilot5000]

hi Hui sir
you made my day , you don't know how much you helped me and I must say for that you deserve more then thank you , you are the best . keep the good work,

 

[Pilot5000]

Hi hui
if you remember last time you helped me you gave me the following code which was to insert columns

Sub Range55()

Range("E2:E5,F2:F6,H2:H6,J2:J6,L2:L6").Delete Shift:=xlUp
  For c = 0 To 7
  Columns("M:M").Offset(0, -c).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Next c
   
   
End Sub

my question is to as follow to save using 2 different codes how I can integrate the insert action to the last code just gave me ??? meaning finding the last column with data and insert empty columns between each 2 columns until column A .My second question is you wrote -For Each c In Range("a1:n1")- what I do when the N is unknown and can be change as I mentioned every week, one week it's "n" and the other week it can be "K" or "W" ? (the A is always know)

 

[Hui]

Mike

Please ask new questions in new posts

Q1. This code will insert columns for you. Place it directly after the last code

Dim Col As Integer
Col = Range("IV2").End(xlToLeft).Column
For i = Col To 2 Step -1
Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next

Q2. Change N1 to any value that is greater than the expected largest column eg: Z1 or AZ1 etc
But don't make it too large as it will slow down the process

 

[Hui]

Mike

Also I don't think I helped you with the problem shown as

Sub Range55()
Range("E2:E5,F2:F6,H2:H6,J2:J6,L2:L6").Delete Shift:=xlUp
...

I don't code ranges like that ?

 

[Pilot5000]

hi hui
first thank you for you help , as you can see I got a suggestion to another cods which is doing the same as your last code that you sent me , What you think about it ????? now to my other question , as you may see I have sheet(sheet3) that come with data from my server in that form , and also sheet (sheet2) with the same data after calling the macros , my problems at this time is that I am looking for macro that delete only the blank rows that under the rows with {SELECT ....} and the under the blank rows with {CELL-ENTER......} and all the rest blank should remain in place , I am also looking for option to integrate the new macro with the existing one and come up with one macro that I can refer to click button , in case that it is unfeasible , it's ok with me , I just will call the other macro at the from the first macro that I have, hope I am not asking for too much but thank you in advance for your time and effort and any ideas are welcome

 

[Pilot5000]

hi
did you got my last message please can you help me ?????