Solved: Newbie VBA help in Excel (Moving cells)

[Ryu]

Hi People

I was hoping some of the more experienced heads of VBA users would be able to help me out here please.

In Excel I have some records from Column A to Column L - however I only require help on the data from column H to L.

What I want to do is run a bit of coding where it will do two things:

1, In column H to L there are some columns with data missing and what I want to do is show each column next to each other.

For example now there is always data shown in column H and then nothing in column I and then some data in column J. In other columns it shows data in column H and then another some in either I J K or L - would it be possible to run some coding where if a cell/s is/are empty then to move the 2nd and then the 3rd etc etc. So in other words there are no empty columns in between columns H and L.

2, This is the more difficult coding I need help with, In some Columns between H and L they have text like ?HYPERI (some text) USING?..? ? What I?m trying to do is in these cells to remove the word ?HYPERI? and everything after and including ?USING?.

If you require any more information then please ask.

Any help please?

[jproffer]

Your first request, as far as I know, is impossible. You can't hide a single cell. You could MOVE the data if you want, but then the cell it's in is changed and what if, say the "original" cell J1 which now has data from K1 or L1, depending on blanks gets new data pasted over the original, which isn't really original, data? Would this ever happen?

Second request, if it's ALWAYS HYPERI and thats all you'll EVER want to leave use the Left Function...

[VBA] =Left(A1,6)
[/VBA]

[rbrhodes]

Hi Ryu,

Your first request means you want to delete any empty COLUMNS? Perhaps post a small example of your workbook to better explain what you want... Before and after examples work best.

For the second request, try this on a COPY of your workbook.

[vba]
Option Explicit
Sub UnHyperUnUsing()
Dim i As Long
Dim cCell As Range
Dim cRange As Range
Dim Lastrow As Long
Dim InUsing As Long
Dim StrUsing As String
'Cols H to L
For i = 8 To 12

'Find last row of data Current Column
Lastrow = Cells(65536, i).End(xlUp).Row

'Set as range
Set cRange = Range(Cells(1, i), Cells(Lastrow, i))
'Do all
For Each cCell In cRange
'Lose "Hyperi"
If UCase(Left(cCell, 7)) = "HYPERI " Then
'Space between "HYPERI and ...some text...
cCell.Value = Right(cCell, Len(cCell) - 7)
ElseIf UCase(Left(cCell, 6)) = "HYPERI" Then
'No space between "HYPERIand ...some text...
cCell.Value = Right(cCell, Len(cCell) - 6)
End If
'Find " Using" (space before)
StrUsing = UCase(cCell)
InUsing = InStr(StrUsing, " USING")
'Delete if found
If InUsing > 0 Then
cCell.Value = Left(cCell, InUsing - 1)
Else
'Find "Using" (no space before)
InUsing = InStr(StrUsing, "USING")
If InUsing > 0 Then
cCell.Value = Left(cCell, InUsing - 1)
End If
End If
'Next row
Next cCell
'Next col
Next i

End Sub

[/vba]

[jproffer]

What I?m trying to do is in these cells to remove the word ?HYPERI? and everything after and including ?USING?.

I really do read these things, lol. My apologies. For some reason I thought you wanted to remove everything except the "HYPERI".

[Ryu]

Hi,

Thanks for all your help guys.

Rbhodes your coding has answered my 2nd problem, thanks you soo much buddy. I know I sounded confusing regarding my 1st problem and I apologize. What I meant was not to hide/delete cells but to move data from one column to another column (not copy and paste but to cut and paste.)

For example I have some random text below:

Before running the code:

ColumnH--------ColumnI--------ColumnJ--------ColumnK--------ColumnL
J:\\HAZ\\HAZ\\S----------------J:\\HAZ\\HAZ\\U---------------J:\\HAZ\\HAZ\\F

Now when I run the code i want to show it as:

ColumnH--------ColumnI--------ColumnJ--------ColumnK--------ColumnL
J:\\HAZ\\HAZ\\S-------J:\\HAZ\\HAZ\\U------J:\\HAZ\\HAZ\\F


So basically in the first set of data the data is shown in Column H, Column J and Column L - however after running the coding I want to display the data in column H column I and column J. (In other words no data is getting deleted but cells that are empty are getting new data pasted over them.) Hope that helps

Just one more thing guys what does option explicit mean?

Thanks

EDIT** Guys Ive solved the first problem - thread resolved

thanks a million people

[rbrhodes]

Ryu,

Option Explicit requires all variables to be declared. It's good practice (I think) to use it as the VBE will catch any typos eg If you have a declared variable "MyRange" and Option Explicit is not used, typing in MyRagne will get through resulting in an error in your code. If Option Explicit is used the code will stop at run time and you will get a message regarding the misspelled variable name and a chance to correct it.

[Ryu]

Thanks Rbrhodes